OTHER QUERIES
REPAY INVOICE DETAILS SELECT aid1.ROWID row_id, aid1.invoice_id invoice_id, aid1.invoice_distribution_id invoice_distribution_id, aid1.prepay_distribution_id prepay_distribution_id, aid2.distribution_line_number prepay_dist_number, (-1) * aid1.amount prepay_amount_applied, aid1.dist_code_combination_id dist_code_combination_id, aid2.accounting_date accounting_date, aid1.period_name period_name, aid1.set_of_books_id set_of_books_id, aid1.description description, aid1.po_distribution_id po_distribution_id, aid1.rcv_transaction_id rcv_transaction_id, aid1.org_id org_id, ai.invoice_num invoice_num, ai.vendor_id vendor_id, ai.vendor_site_id vendor_site_id, aid2.invoice_id prepay_id, ai2.invoice_num prepayment_invoice_num FROM ap_invoices_all ai, ap_invoice_distributions_all aid1, ap_invoice_distributions_all aid2, ap_invoices_all ai2 WHERE aid1.prepay_distribution_id = aid2.invoice_distribution_id AND ai.invoice_id = aid1.invoice_id AND aid1.amount < 0 AND NVL (aid1.reversal_flag, 'N') != 'Y' AND aid1.line_type_lookup_code = 'PREPAY' AND ai.invoice_type_lookup_code NOT IN('PREPAYMENT', 'CREDIT', 'DEBIT') and ai2.invoice_id = aid2.invoice_id / PO ALL RECEIPTS SELECT RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID, PO_VENDORS.VENDOR_NAME, DECODE(RCV_SHIPMENT_HEADERS.VENDOR_SITE_ID, NULL, NULL,PO_VENDOR_SITES_ALL.VENDOR_SITE_CODE) VENDOR_SITE, PO_VENDOR_SITES_ALL.ADDRESS_LINE1, PO_VENDOR_SITES_ALL.ADDRESS_LINE2, PO_VENDOR_SITES_ALL.ADDRESS_LINE3, PO_VENDOR_SITES_ALL.CITY, PO_VENDOR_SITES_ALL.STATE, PO_VENDOR_SITES_ALL.COUNTRY, DECODE(RCV_SHIPMENT_LINES.PO_HEADER_ID,NULL,'Unordered','Ordered') AS RECEIPT_TYPE, RCV_SHIPMENT_HEADERS.RECEIPT_NUM, RCV_SHIPMENT_HEADERS.CREATION_DATE AS RECEIPT_DATE, RCV_SHIPMENT_LINES.SHIP_TO_LOCATION_ID, HR_LOCATIONS.LOCATION_CODE||'-'||HR_LOCATIONS.DESCRIPTION AS SHIP_TO_LOCATION, RCV_SHIPMENT_HEADERS.SHIP_TO_ORG_ID, HR_ALL_ORGANIZATION_UNITS_TL.NAME AS ORGANIZATION, RCV_SHIPMENT_HEADERS.EMPLOYEE_ID, PER_ALL_PEOPLE_F.FULL_NAME, RCV_SHIPMENT_HEADERS.PACKING_SLIP, RCV_SHIPMENT_HEADERS.SHIPPED_DATE, RCV_SHIPMENT_HEADERS.FREIGHT_CARRIER_CODE, RCV_SHIPMENT_HEADERS.FREIGHT_TERMS, RCV_SHIPMENT_LINES.CATEGORY_ID, MTL_CATEGORIES_V.SEGMENT1||'.'||MTL_CATEGORIES_V.SEGMENT2 AS CATEGORY_SUBCAT, RCV_SHIPMENT_LINES.QUANTITY_SHIPPED, RCV_SHIPMENT_LINES.QUANTITY_RECEIVED, RCV_SHIPMENT_LINES.UNIT_OF_MEASURE, RCV_SHIPMENT_LINES.ITEM_DESCRIPTION, PO_HEADERS.SEGMENT1 AS PO_NUM, PO_HEADERS.CREATION_DATE AS PO_DATE, RCV_SHIPMENT_LINES.PO_LINE_ID, RCV_SHIPMENT_LINES.PO_DISTRIBUTION_ID, RCV_SHIPMENT_LINES.REQUISITION_LINE_ID, RCV_SHIPMENT_LINES.DELIVER_TO_PERSON_ID, DISC_RCV_DELIVERY.DELIVERY_DATE, RCV_SHIPMENT_HEADERS.COMMENTS AS HEADER_NOTE, DISC_PO_DISTRIBUTION_DSC.GL_NAME_SEG1, DISC_PO_DISTRIBUTION_DSC.GL_NAME_SEG2, DISC_PO_DISTRIBUTION_DSC.GL_NAME_SEG3, DISC_PO_DISTRIBUTION_DSC.GL_NAME_SEG4, DISC_PO_DISTRIBUTION_DSC.GL_NAME_SEG5, DISC_PO_DISTRIBUTION_DSC.GL_NAME_SEG6, DISC_PO_DISTRIBUTION_DSC.QUANTITY_ORDERED, DISC_PO_DISTRIBUTION_DSC.QUANTITY_BILLED, DISC_PO_DISTRIBUTION_DSC.QUANTITY_DELIVERED , PO_LINES_ALL.UNIT_PRICE, PO_HEADERS.CURRENCY_CODE, PO_LINE_LOCATIONS_ALL.PROMISED_DATE, DECODE(RCV_SHIPMENT_LINES.ROUTING_HEADER_ID, 2, 'Yes','No') AS INSPECTION_REQUIRED, DISC_RCV_ACCEPTANCE.INSPECTION_STATUS_CODE, DISC_RCV_RETURN.RETURN_DATE, DISC_RCV_RETURN.TRANSACTION_TYPE AS RETURN_TRANSACTION_CODE FROM RCV_SHIPMENT_HEADERS RCV_SHIPMENT_HEADERS, RCV_SHIPMENT_LINES RCV_SHIPMENT_LINES, DISC_RCV_DELIVERY DISC_RCV_DELIVERY, DISC_RCV_RETURN DISC_RCV_RETURN, PO_VENDORS PO_VENDORS, PO_VENDOR_SITES_ALL PO_VENDOR_SITES_ALL, HR_LOCATIONS HR_LOCATIONS, HR_ALL_ORGANIZATION_UNITS_TL HR_ALL_ORGANIZATION_UNITS_TL, PER_ALL_PEOPLE_F PER_ALL_PEOPLE_F, PO_HEADERS_ALL PO_HEADERS, MTL_CATEGORIES_V MTL_CATEGORIES_V, DISC_PO_DISTRIBUTION_DSC DISC_PO_DISTRIBUTION_DSC, PO_LINES_ALL PO_LINES_ALL, PO_LINE_LOCATIONS_ALL PO_LINE_LOCATIONS_ALL, DISC_RCV_ACCEPTANCE WHERE RCV_SHIPMENT_HEADERS.SHIPMENT_HEADER_ID = RCV_SHIPMENT_LINES.SHIPMENT_HEADER_ID AND RCV_SHIPMENT_HEADERS.VENDOR_ID = PO_VENDORS.VENDOR_ID AND PO_VENDORS.VENDOR_ID = PO_VENDOR_SITES_ALL.VENDOR_ID AND NVL(RCV_SHIPMENT_HEADERS.VENDOR_SITE_ID,(SELECT MAX(s.vendor_site_id) FROM po_vendor_sites_all s WHERE s.vendor_id = PO_VENDORS.VENDOR_ID ) ) = PO_VENDOR_SITES_ALL.VENDOR_SITE_ID AND RCV_SHIPMENT_LINES.SHIP_TO_LOCATION_ID = HR_LOCATIONS.LOCATION_ID AND RCV_SHIPMENT_HEADERS.SHIP_TO_ORG_ID = HR_ALL_ORGANIZATION_UNITS_TL.ORGANIZATION_ID AND RCV_SHIPMENT_HEADERS.EMPLOYEE_ID = PER_ALL_PEOPLE_F.PERSON_ID (+) AND RCV_SHIPMENT_LINES.PO_HEADER_ID = PO_HEADERS.PO_HEADER_ID (+) AND RCV_SHIPMENT_LINES.PO_DISTRIBUTION_ID = DISC_PO_DISTRIBUTION_DSC.PO_DISTRIBUTION_ID (+) AND RCV_SHIPMENT_LINES.SHIPMENT_HEADER_ID = DISC_RCV_DELIVERY.SHIPMENT_HEADER_ID (+) AND RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID = DISC_RCV_DELIVERY.SHIPMENT_LINE_ID (+) AND RCV_SHIPMENT_LINES.PO_LINE_ID = PO_LINES_ALL.PO_LINE_ID (+) AND RCV_SHIPMENT_LINES.PO_LINE_LOCATION_ID = PO_LINE_LOCATIONS_ALL.LINE_LOCATION_ID (+) AND MTL_CATEGORIES_V.CATEGORY_ID = RCV_SHIPMENT_LINES.CATEGORY_ID AND RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID = DISC_RCV_ACCEPTANCE.SHIPMENT_LINE_ID (+) AND RCV_SHIPMENT_LINES.SHIPMENT_LINE_ID = DISC_RCV_RETURN.SHIPMENT_LINE_ID (+) AND DECODE(RCV_SHIPMENT_HEADERS.EMPLOYEE_ID,NULL,SYSDATE,RCV_SHIPMENT_HEADERS.CREATION_DATE) BETWEEN DECODE(RCV_SHIPMENT_HEADERS.EMPLOYEE_ID,NULL,SYSDATE,PER_ALL_PEOPLE_F.effective_start_date) AND DECODE(RCV_SHIPMENT_HEADERS.EMPLOYEE_ID,NULL,SYSDATE,PER_ALL_PEOPLE_F.effective_end_date) / PO DETAILS QUERY SELECT disc_gl_set_of_books.set_of_books_id, disc_gl_set_of_books.description AS set_of_books, disc_gl_set_of_books.set_of_book_currency, po_headers_all.segment1 AS po_number, po_headers_all.creation_date AS po_date, po_headers_all.revision_num AS po_revision_num, po_headers_all.currency_code, po_headers_all.rate_type exchange_rate_type, po_headers_all.rate_date exchange_rate_date, po_headers_all.rate exchange_rate, po_headers_all.comments AS description, po_headers_all.type_lookup_code AS po_type, NVL (po_headers_all.approved_flag, 'N') AS approved_flag, po_headers_all.approved_date, po_headers_all.fob_lookup_code AS fob, po_headers_all.freight_terms_lookup_code AS freight_terms, po_headers_all.closed_code AS closure_status, po_headers_all.authorization_status AS approval_status, --PO_HEADERS_ALL.TERMS_ID, ap_terms.NAME AS payment_terms, po_lines_all.item_description, disc_item_master.segment1 AS item_code, po_lines_all.list_price_per_unit, po_lines_all.unit_price, po_lines_all.closed_code AS line_closed_code, po_distributions_all.quantity_billed, po_distributions_all.quantity_cancelled, po_distributions_all.quantity_delivered, po_line_locations_all.quantity_received, disc_po_accepted_qty.quantity quantity_accepted, po_distributions_all.quantity_ordered, po_lines_all.category_id, NVL (mtl_categories_v.description, 'Undefined' ) AS category_description, mtl_categories_v.segment1 AS CATEGORY, mtl_categories_v.segment2 AS sub_category, ( po_lines_all.unit_price * po_distributions_all.quantity_ordered * DECODE (po_distributions_all.rate, NULL, 1, po_distributions_all.rate ) ) AS po_value, ( po_lines_all.unit_price * po_distributions_all.quantity_delivered * DECODE (po_distributions_all.rate, NULL, 1, po_distributions_all.rate ) ) AS po_received_value, ( po_lines_all.unit_price * po_distributions_all.quantity_billed * DECODE (po_distributions_all.rate, NULL, 1, po_distributions_all.rate ) ) AS po_billed_value, ( po_lines_all.unit_price * po_distributions_all.quantity_cancelled * DECODE (po_distributions_all.rate, NULL, 1, po_distributions_all.rate ) ) AS po_cancelled_value, --DISC_PO_TAX.TOTAL_AMOUNT AS TOTAL_TAX_AMOUNT, 0 AS total_tax_amount, po_headers_all.creation_date AS po_created_date, po_distributions_all.project_id, po_distributions_all.task_id, po_distributions_all.expenditure_type, po_distributions_all.expenditure_organization_id, po_distributions_all.expenditure_item_date, po_vendors.vendor_id, po_vendors.vendor_name, po_headers_all.vendor_site_id, po_headers_all.closed_code, --PO_DISTRIBUTIONS_ALL.CODE_COMBINATION_ID AS PO_CCID, po_ccid_dsc.gl_seg1 AS charge_gl_seg1, po_ccid_dsc.gl_seg2 AS charge_gl_seg2, po_ccid_dsc.gl_seg3 AS charge_gl_seg3, po_ccid_dsc.gl_seg4 AS charge_gl_seg4, po_ccid_dsc.gl_seg5 AS charge_gl_seg5, po_ccid_dsc.gl_seg6 AS charge_gl_seg6, po_ccid_dsc.gl_seg2 AS charge_gl_seg7, po_ccid_dsc.gl_seg3 AS charge_gl_seg8, po_ccid_dsc.gl_seg4 AS charge_gl_seg9, po_ccid_dsc.gl_seg5 AS charge_gl_seg10, po_ccid_dsc.gl_seg6 AS charge_gl_seg11, po_ccid_dsc.gl_name_seg1 AS charge_gl_name_seg1, po_ccid_dsc.gl_name_seg2 AS charge_gl_name_seg2, po_ccid_dsc.gl_name_seg3 AS charge_gl_name_seg3, po_ccid_dsc.gl_name_seg4 AS charge_gl_name_seg4, po_ccid_dsc.gl_name_seg5 AS charge_gl_name_seg5, po_ccid_dsc.gl_name_seg6 AS charge_gl_name_seg6, po_ccid_dsc.gl_name_seg2 AS charge_gl_name_seg7, po_ccid_dsc.gl_name_seg3 AS charge_gl_name_seg8, po_ccid_dsc.gl_name_seg4 AS charge_gl_name_seg9, po_ccid_dsc.gl_name_seg5 AS charge_gl_name_seg10, po_ccid_dsc.gl_name_seg6 AS charge_gl_name_seg11, po_ccid_accrual.gl_seg1 AS accrual_gl_seg1, po_ccid_accrual.gl_seg2 AS accrual_gl_seg2, po_ccid_accrual.gl_seg3 AS accrual_gl_seg3, po_ccid_accrual.gl_seg4 AS accrual_gl_seg4, po_ccid_accrual.gl_seg5 AS accrual_gl_seg5, po_ccid_accrual.gl_seg6 AS accrual_gl_seg6, po_ccid_accrual.gl_seg2 AS accrual_gl_seg7, po_ccid_accrual.gl_seg3 AS accrual_gl_seg8, po_ccid_accrual.gl_seg4 AS accrual_gl_seg9, po_ccid_accrual.gl_seg5 AS accrual_gl_seg10, po_ccid_accrual.gl_seg6 AS accrual_gl_seg11, po_ccid_accrual.gl_name_seg1 AS accrual_gl_name_seg1, po_ccid_accrual.gl_name_seg2 AS accrual_gl_name_seg2, po_ccid_accrual.gl_name_seg3 AS accrual_gl_name_seg3, po_ccid_accrual.gl_name_seg4 AS accrual_gl_name_seg4, po_ccid_accrual.gl_name_seg5 AS accrual_gl_name_seg5, po_ccid_accrual.gl_name_seg6 AS accrual_gl_name_seg6, po_ccid_accrual.gl_name_seg2 AS accrual_gl_name_seg7, po_ccid_accrual.gl_name_seg3 AS accrual_gl_name_seg8, po_ccid_accrual.gl_name_seg4 AS accrual_gl_name_seg9, po_ccid_accrual.gl_name_seg5 AS accrual_gl_name_seg10, po_ccid_accrual.gl_name_seg6 AS accrual_gl_name_seg11, disc_po_status_reason.note AS status_reason, disc_pro_req_dist_map.req_num as req_number FROM po_headers_all po_headers_all, po_lines_all po_lines_all, po_distributions_all po_distributions_all, po_vendors po_vendors, disc_ccid_dsc_mv po_ccid_dsc, disc_ccid_dsc_mv po_ccid_accrual, po_line_locations_all po_line_locations_all, mtl_categories_v mtl_categories_v, ap_terms ap_terms, --DISC_PO_TAX DISC_PO_TAX , apps.disc_gl_set_of_books disc_gl_set_of_books, disc_po_status_reason disc_po_status_reason, disc_po_accepted_qty disc_po_accepted_qty, disc_item_master disc_item_master, disc_pro_req_dist_map disc_pro_req_dist_map WHERE po_distributions_all.po_header_id = po_headers_all.po_header_id AND po_distributions_all.po_line_id = po_lines_all.po_line_id AND po_distributions_all.line_location_id = po_line_locations_all.line_location_id AND po_headers_all.po_header_id = po_lines_all.po_header_id AND po_line_locations_all.po_header_id = po_lines_all.po_header_id AND po_line_locations_all.po_line_id = po_lines_all.po_line_id AND NVL (po_line_locations_all.cancel_flag, 'N') = 'N' AND po_vendors.vendor_id = po_headers_all.vendor_id AND po_ccid_dsc.code_combination_id = po_distributions_all.code_combination_id AND po_ccid_accrual.code_combination_id = po_distributions_all.accrual_account_id AND mtl_categories_v.category_id = po_lines_all.category_id AND po_headers_all.terms_id = ap_terms.term_id(+) AND disc_gl_set_of_books.set_of_books_id = po_distributions_all.set_of_books_id AND po_headers_all.po_header_id = disc_po_status_reason.object_id(+) AND po_distributions_all.po_header_id = disc_po_accepted_qty.po_header_id(+) AND po_distributions_all.po_line_id = disc_po_accepted_qty.po_line_id(+) AND po_distributions_all.line_location_id = disc_po_accepted_qty.po_line_location_id(+) AND disc_item_master.inventory_item_id = po_lines_all.item_id -- AND po_distributions_all.req_header_reference_num = po_requisition_headers_all.requisition_header_id(+) -- AND po_distributions_all.req_line_reference_num = po_requisition_lines_all.requisition_line_id(+) AND po_distributions_all.REQ_DISTRIBUTION_ID = disc_pro_req_dist_map.DISTRIBUTION_ID (+) / AP PAYMENT ANALYSYS SELECT ap_invoice_payments_all.accounting_date, ap_invoice_payments_all.amount, ap_invoice_payments_all.check_id, ap_invoice_payments_all.invoice_id, ap_invoice_payments_all.invoice_payment_id, ap_invoice_payments_all.payment_num, ap_invoice_payments_all.bank_account_num, ap_invoices_all.invoice_num, --ap_invoices_all.invoice_date, AP_INVOICES_ALL.DOC_SEQUENCE_VALUE , ap_invoices_all.invoice_currency_code, ap_invoices_all.payment_currency_code, ap_invoices_all.vendor_id, ap_invoices_all.vendor_site_id, po_vendors.vendor_name, ap_invoices_all.invoice_amount, ap_invoices_all.amount_paid, ap_invoices_all.invoice_date, ap_invoices_all.description, ap_invoices_all.tax_amount, po_vendor_sites_all.vendor_site_code, ap_checks_all.amount check_amount, ap_checks_all.bank_account_id, ap_checks_all.bank_account_name, ap_checks_all.address_line1, ap_checks_all.address_line2, ap_checks_all.address_line3, ap_checks_all.city, --ap_checks_all.vendor_name, --ap_checks_all.vendor_site_code, --ap_checks_all.bank_account_num, ap_checks_all.bank_num, ap_checks_all.check_voucher_num, --ap_invoices_all.attribute6 AS "LOA/WO/PO NUM", --ap_invoices_all.attribute7 AS "C-Form NUM" , disc_gl_month.period_name, --Added by CV AP_CHECKS_ALL.STATUS_LOOKUP_CODE, AP_CHECKS_ALL.CHECK_NUMBER, AP_CHECKS_ALL.PAYMENT_TYPE_FLAG, AP_CHECKS_ALL.DOC_SEQUENCE_ID, AP_CHECKS_ALL.DOC_SEQUENCE_VALUE FROM ap_invoice_payments_all, ap_invoices_all ap_invoices_all, po.po_vendors po_vendors, ap_checks_all ap_checks_all, po.po_vendor_sites_all po_vendor_sites_all, disc_gl_month disc_gl_month WHERE ap_invoice_payments_all.reversal_flag <> 'y' AND ap_invoices_all.invoice_id = ap_invoice_payments_all.invoice_id AND po_vendors.vendor_id = ap_invoices_all.vendor_id AND po_vendor_sites_all.vendor_site_id = ap_invoices_all.vendor_site_id AND ap_invoice_payments_all.check_id = ap_checks_all.check_id (+) and ap_invoice_payments_all.accounting_date between disc_gl_month.start_date and disc_gl_month.end_date / AP INVOICE QUERY SELECT aiv.vendor_name, vendor_number, aiv.vendor_site_code, NVL (aiv.amount_paid, 0) amount_paid, aiv.approved_amount, aiv.description, aiv.doc_sequence_value, aiv.expenditure_item_date, aiv.expenditure_type, NVL (aiv.invoice_amount, 0) invoice_amount, aiv.invoice_currency_code, aiv.invoice_date, aiv.invoice_id, aiv.invoice_num, aiv.base_amount, aiv.invoice_type_lookup_code, aiv.terms_date, aiv.batch_name, aiv.invoice_type, aiv.payment_status, aiv.actual_invoice_amount, aiv.doc_sequence_name, aiv.set_of_books_id, aiv.org_id, aiv.set_of_books_name, aiv.expenditure_organization_name, aiv.distribution_total, aiv.approval_status_lookup_code, aiv.po_number, aiv.receipt_number, aiv.gl_date, aiv.period_name, aiv.prepayments_applied_flag, aiv.payments_exist_flag, aiv.prepay_amount_applied, aiv.project, aiv.task, aiv.pay_curr_invoice_amount, NVL (aiv.invoice_amount, 0) - NVL (aiv.amount_paid, 0) net_due, -DECODE (aiv.invoice_type, 'Prepayment', DECODE (aiv.approval_status_lookup_code, 'AVAILABLE', - (NVL (aiv.amount_paid, 0 ) ) ), 0 ) advance, ffvt.description liability_account, ( segment1 || '.' || segment2 || '.' || segment3 || '.' || segment4 || '.' || segment5 || '.' || segment6 || '.' || segment7 || '.' || segment8 || '.' || segment9 || '.' || segment10 || '.' || segment11 ) liability_code, aiv.POSTING_STATUS, decode(aiv.POSTING_FLAG,'Y','Accounted', 'P','Partial', 'N', 'Not Accounted','Other') as POSTING_FLAG, aiv.EXCHANGE_RATE --NVL(NET_DUE,0)+NVL(ADVANCE,0) NET_OUTSTANDING FROM DISC_AP_INVOICES_ALL_V aiv, gl_code_combinations gcc, fnd_flex_values_vl ffvt WHERE gcc.code_combination_id = aiv.accts_pay_code_combination_id AND gcc.segment2 = ffvt.flex_value AND ffvt.flex_value_set_id = 1008011 and aiv.invoice_id in (select distinct(invoice_id) from AP_INVOICE_DISTRIBUTIONS_ALL) / AP INVOICE DETAIL SELECT disc_gl_set_of_books.set_of_books_id, disc_gl_set_of_books.description AS set_of_books, disc_gl_set_of_books.set_of_book_currency AS set_of_book_currency, disc_ccid_dsc_mv.gl_seg1, disc_ccid_dsc_mv.gl_seg2, disc_ccid_dsc_mv.gl_seg3, disc_ccid_dsc_mv.gl_seg4, disc_ccid_dsc_mv.gl_seg5, disc_ccid_dsc_mv.gl_seg6, disc_ccid_dsc_mv.gl_seg7, disc_ccid_dsc_mv.gl_seg8, disc_ccid_dsc_mv.gl_seg9, disc_ccid_dsc_mv.gl_seg10, disc_ccid_dsc_mv.gl_seg11, disc_ccid_dsc_mv.gl_name_seg1, disc_ccid_dsc_mv.gl_name_seg2, disc_ccid_dsc_mv.gl_name_seg3, disc_ccid_dsc_mv.gl_name_seg4, disc_ccid_dsc_mv.gl_name_seg5, disc_ccid_dsc_mv.gl_name_seg6, disc_ccid_dsc_mv.gl_name_seg7, disc_ccid_dsc_mv.gl_name_seg8, disc_ccid_dsc_mv.gl_name_seg9, disc_ccid_dsc_mv.gl_name_seg10, disc_ccid_dsc_mv.gl_name_seg11, disc_ccid_dsc_mv1.gl_seg2 as liab_gl_seg2, disc_ccid_dsc_mv1.gl_name_seg2 as liab_gl_name_seg2, ap_invoices_all.invoice_num, ap_invoices_all.invoice_date, INITCAP (ap_invoices_all.invoice_type_lookup_code ) invoice_type_lookup_code, --AP_INVOICES_ALL.INVOICE_AMOUNT, ap_invoices_all.invoice_currency_code, ap_invoices_all.payment_currency_code, --AP_INVOICES_ALL.DESCRIPTION, --AP_INVOICES_ALL.EXCHANGE_RATE, ap_invoices_all.vendor_id, ap_invoices_all.vendor_site_id, po_vendor_sites_all.vendor_site_code, ap_invoice_distributions_all.accounting_date, ap_invoice_distributions_all.description, ap_invoice_distributions_all.amount, ap_invoice_distributions_all.base_amount, ap_invoice_distributions_all.period_name, ap_invoice_distributions_all.exchange_rate, ap_invoice_distributions_all.attribute4, --AP_INVOICES_PKG.GET_POSTING_STATUS(AP_INVOICES_ALL.INVOICE_ID) POSTING_FLAG , ap_invoices_pkg.get_approval_status (ap_invoices_all.invoice_id, ap_invoices_all.invoice_amount, ap_invoices_all.payment_status_flag, ap_invoices_all.invoice_type_lookup_code ) approval_status_lookup_code, --AP_INVOICES_PKG.SELECTED_FOR_PAYMENT_FLAG(AP_INVOICES_ALL.INVOICE_ID) SELECTED_FOR_PAYMENT_FLAG , --AP_INVOICES_PKG.GET_UNPOSTED_VOID_PAYMENT (AP_INVOICES_ALL.INVOICE_ID) UNPOSTED_VOID_PAYMENT_FLAG , --AP_INVOICES_PKG.GET_DISCOUNT_PAY_DISTS_FLAG (AP_INVOICES_ALL.INVOICE_ID) DISCOUNT_PAY_DISTS_FLAG , --AP_INVOICES_PKG.GET_PREPAYMENTS_APPLIED_FLAG (AP_INVOICES_ALL.INVOICE_ID) PREPAYMENTS_APPLIED_FLAG , --AP_INVOICES_PKG.GET_PAYMENTS_EXIST_FLAG (AP_INVOICES_ALL.INVOICE_ID) PAYMENTS_EXIST_FLAG , --DECODE(AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', AP_INVOICES_PKG.GET_PREPAY_AMOUNT_APPLIED (AP_INVOICES_ALL.INVOICE_ID), 0) PREPAY_AMOUNT_APPLIED , --AP_INVOICES_ALL.APPROVAL_STATUS APPROVAL_STATUS , ap_invoices_all.posting_status posting_status, ap_invoices_pkg.get_po_number (ap_invoices_all.invoice_id) po_number, ap_invoices_pkg.get_release_number (ap_invoices_all.invoice_id) release_number, ap_invoices_pkg.get_receipt_number (ap_invoices_all.invoice_id) receipt_number, ap_invoices_pkg.get_amount_withheld (ap_invoices_all.invoice_id) amount_withheld, ap_invoices_pkg.get_prepaid_amount (ap_invoices_all.invoice_id) prepaid_amount, alc4.displayed_field wfapproval_status_dsp, ap_invoices_all.approved_amount, ap_invoices_all.amount_paid, --ap_invoices_all.description, ap_invoices_all.tax_amount, po_vendors.vendor_name, INITCAP (NVL (po_vendors.vendor_type_lookup_code, 'VENDOR') ) vendor_type_lookup_code, ja_in_ap_tds_invoices.invoice_amount, ja_in_ap_tds_invoices.dm_invoice_num, ja_in_ap_tds_invoices.tds_invoice_num, ja_in_ap_tds_invoices.tds_tax_id, ja_in_ap_tds_invoices.tds_section, ja_in_ap_tds_invoices.tds_tax_rate, ja_in_ap_tds_invoices.tds_amount, ap_invoice_distributions_all.task_id, ap_invoice_distributions_all.expenditure_organization_id, ap_invoices_all.invoice_id, ap_invoices_all.doc_sequence_id doc_sequence_id, ap_invoices_all.doc_sequence_value doc_sequence_value, disc_prepay_invoice_details.prepay_amount_applied, disc_prepay_invoice_details.prepayment_invoice_num FROM ap.ap_invoices_all ap_invoices_all, ap.ap_invoice_distributions_all ap_invoice_distributions_all, apps.disc_gl_set_of_books disc_gl_set_of_books, apps.disc_ccid_dsc_mv disc_ccid_dsc_mv, po.po_vendors po_vendors, po.po_vendor_sites_all po_vendor_sites_all, ja.ja_in_ap_tds_invoices ja_in_ap_tds_invoices, disc_prepay_invoice_details disc_prepay_invoice_details, --AP_LOOKUP_CODES ALC1, --AP_LOOKUP_CODES ALC2, --AP_LOOKUP_CODES ALC3, ap_lookup_codes alc4, apps.disc_ccid_dsc_mv disc_ccid_dsc_mv1 WHERE ap_invoice_distributions_all.invoice_id = ap_invoices_all.invoice_id AND ap_invoice_distributions_all.set_of_books_id = disc_gl_set_of_books.set_of_books_id AND ap_invoice_distributions_all.dist_code_combination_id = disc_ccid_dsc_mv.code_combination_id AND ap_invoice_distributions_all.reversal_flag IS NULL AND po_vendors.vendor_id = ap_invoices_all.vendor_id AND ap_invoices_all.vendor_site_id = po_vendor_sites_all.vendor_site_id AND ap_invoice_distributions_all.invoice_id = ja_in_ap_tds_invoices.invoice_id(+) AND po_vendors.vendor_id = po_vendor_sites_all.vendor_id AND alc4.lookup_type(+) = 'AP_WFAPPROVAL_STATUS' AND alc4.lookup_code(+) = ap_invoices_all.wfapproval_status AND ap_invoice_distributions_all.invoice_distribution_id = disc_prepay_invoice_details.invoice_distribution_id(+) and ap_invoices_all.ACCTS_PAY_CODE_COMBINATION_ID = disc_ccid_dsc_mv1.code_combination_id (+) / CUST LIABILITY BALANCE SELECT 'Reached GL From AP' SOURCE, GLB.NAME sob_name, alb.accounting_date, pov.vendor_id, pov.vendor_name, api.invoice_id, api.invoice_num, api.doc_sequence_value, lia.gl_seg2 ACCOUNT, lia.gl_name_seg2 account_desc, lia.gl_seg1 Entity, lia.GL_NAME_SEG1 Entity_desc, api.INVOICE_DATE, SUM (NVL (accounted_dr, 0)) - SUM (NVL (accounted_cr, 0)) balance FROM ap_liability_balance alb, po_vendors pov, ap_invoices_all api, disc_ccid_dsc_mv lia, gl_sets_of_books GLB WHERE alb.vendor_id = pov.vendor_id AND alb.invoice_id = api.invoice_id AND pov.vendor_id = api.vendor_id AND alb.code_combination_id = lia.code_combination_id AND alb.set_of_books_id = GLB.set_of_books_id AND lia.account_type = 'Liability' GROUP BY GLB.NAME, alb.accounting_date, pov.vendor_id, pov.vendor_name, api.doc_sequence_value, api.invoice_id, api.invoice_num, lia.gl_seg2, lia.gl_name_seg2, lia.gl_seg1, lia.gl_name_seg1, api.INVOICE_DATE UNION ALL SELECT /*+ ORDERED USE_NL(JEL JEH JEB CAT SRC) INDEX(JEL GL_JE_LINES_N1) INDEX(JEH GL_JE_HEADERS_U1) INDEX(JEB GL_JE_BATCHES_U1) INDEX(CAT GL_JE_CATEGORIES_U1) INDEX(SRC GL_JE_SOURCES_U1) */ DECODE (src.user_je_source_name, 'Payables', 'Debit on Liability', src.user_je_source_name ), gsb.NAME, jel.effective_date effective_date, 0, DECODE (src.user_je_source_name, 'Payables', jel.reference_1, NULL ), 0, DECODE (src.user_je_source_name, 'Payables', jel.reference_5, NULL ), DECODE (src.user_je_source_name, 'Payables', jel.subledger_doc_sequence_value, jeh.doc_sequence_value ), cc.gl_seg2, cc.gl_name_seg2, cc.GL_SEG1, cc.GL_NAME_SEG1, jel.INVOICE_DATE, SUM (NVL (accounted_dr, 0)) - SUM (NVL (accounted_cr, 0)) balance FROM disc_ccid_dsc_mv cc, gl_sets_of_books gsb, gl_je_lines jel, gl_je_headers jeh, gl_je_batches jeb, gl_je_categories cat, gl_je_sources src, fnd_user fu WHERE jel.code_combination_id = cc.code_combination_id AND jel.status || '' = 'P' AND (accounted_dr != 0 OR accounted_cr != 0) AND jeh.je_header_id = jel.je_header_id AND jeh.actual_flag = 'A' AND jeb.je_batch_id = jeh.je_batch_id AND jeb.set_of_books_id = gsb.set_of_books_id AND jeb.average_journal_flag = 'N' AND src.je_source_name = jeh.je_source AND cat.je_category_name = jeh.je_category AND ( (jeh.je_source NOT IN ('Payables')) OR ( jeh.je_source IN ('Payables') AND jel.reference_10 != 'LIABILITY' ) ) AND jeh.last_updated_by = fu.user_id(+) AND cc.account_type = 'Liability' GROUP BY src.user_je_source_name, gsb.NAME, jel.effective_date, jeh.NAME, jel.reference_1, jel.reference_5, jel.subledger_doc_sequence_value, jeh.doc_sequence_value, cc.gl_seg2, cc.gl_name_seg2, cc.gl_seg1, cc.GL_NAME_SEG1, jel.INVOICE_DATE UNION ALL SELECT 'Not in GL Yet' SOURCE, GLB.NAME sob_name, aph.accounting_date, pov.vendor_id, pov.vendor_name, api.invoice_id, api.invoice_num, api.doc_sequence_value, lia.gl_seg2 ACCOUNT, lia.gl_name_seg2 account_desc, lia.gl_seg1 Entity, lia.GL_NAME_SEG1 Entity_desc, api.INVOICE_DATE, SUM (NVL (accounted_dr, 0)) - SUM (NVL (accounted_cr, 0)) balance FROM ap_ae_lines_all apl, ap_ae_headers_all aph, gl_sets_of_books GLB, po_vendors pov, ap_invoices_all api, disc_ccid_dsc_mv lia WHERE apl.ae_line_type_code = 'LIABILITY' AND apl.gl_sl_link_id IS NULL AND apl.ae_header_id = aph.ae_header_id AND apl.third_party_id = pov.vendor_id AND apl.reference2 = api.invoice_id AND pov.vendor_id = api.vendor_id AND apl.code_combination_id = lia.code_combination_id AND aph.set_of_books_id = GLB.set_of_books_id AND lia.account_type = 'Liability' GROUP BY GLB.NAME, aph.accounting_date, pov.vendor_id, pov.vendor_name, api.doc_sequence_value, api.invoice_id, api.invoice_num, lia.gl_seg2, lia.gl_name_seg2, lia.gl_seg1, lia.gl_name_seg1, api.INVOICE_DATE / CUST LIABILITY ADVANCE BAL SELECT 'Reached GL From AP' SOURCE, GLB.NAME sob_name, alb.accounting_date, pov.vendor_id, pov.vendor_name, api.invoice_id, api.invoice_num, api.doc_sequence_value, lia.gl_seg2 ACCOUNT, lia.gl_name_seg2 account_desc, lia.gl_seg1 Entity, lia.GL_NAME_SEG1 Entity_desc, api.INVOICE_DATE, SUM (NVL (accounted_dr, 0)) - SUM (NVL (accounted_cr, 0)) balance FROM ap_liability_balance alb, po_vendors pov, ap_invoices_all api, disc_ccid_dsc_mv lia, gl_sets_of_books GLB WHERE alb.vendor_id = pov.vendor_id AND alb.invoice_id = api.invoice_id AND pov.vendor_id = api.vendor_id AND alb.code_combination_id = lia.code_combination_id AND alb.set_of_books_id = GLB.set_of_books_id AND lia.account_type IN ('Liability','Asset') GROUP BY GLB.NAME, alb.accounting_date, pov.vendor_id, pov.vendor_name, api.doc_sequence_value, api.invoice_id, api.invoice_num, lia.gl_seg2, lia.gl_name_seg2, lia.gl_seg1, lia.gl_name_seg1, api.INVOICE_DATE UNION ALL SELECT /*+ ORDERED USE_NL(JEL JEH JEB CAT SRC) INDEX(JEL GL_JE_LINES_N1) INDEX(JEH GL_JE_HEADERS_U1) INDEX(JEB GL_JE_BATCHES_U1) INDEX(CAT GL_JE_CATEGORIES_U1) INDEX(SRC GL_JE_SOURCES_U1) */ DECODE (src.user_je_source_name, 'Payables', 'Debit on Liability', src.user_je_source_name ), gsb.NAME, jel.effective_date effective_date, 0, DECODE (src.user_je_source_name, 'Payables', jel.reference_1, NULL ), 0, DECODE (src.user_je_source_name, 'Payables', jel.reference_5, NULL ), DECODE (src.user_je_source_name, 'Payables', jel.subledger_doc_sequence_value, jeh.doc_sequence_value ), cc.gl_seg2, cc.gl_name_seg2, cc.GL_SEG1, cc.GL_NAME_SEG1, jel.INVOICE_DATE, SUM (NVL (accounted_dr, 0)) - SUM (NVL (accounted_cr, 0)) balance FROM disc_ccid_dsc_mv cc, gl_sets_of_books gsb, gl_je_lines jel, gl_je_headers jeh, gl_je_batches jeb, gl_je_categories cat, gl_je_sources src, fnd_user fu WHERE jel.code_combination_id = cc.code_combination_id AND jel.status || '' = 'P' AND (accounted_dr != 0 OR accounted_cr != 0) AND jeh.je_header_id = jel.je_header_id AND jeh.actual_flag = 'A' AND jeb.je_batch_id = jeh.je_batch_id AND jeb.set_of_books_id = gsb.set_of_books_id AND jeb.average_journal_flag = 'N' AND src.je_source_name = jeh.je_source AND cat.je_category_name = jeh.je_category AND ( (jeh.je_source NOT IN ('Payables')) OR ( jeh.je_source IN ('Payables') AND jel.reference_10 not IN ('Liability') ) ) AND jeh.last_updated_by = fu.user_id(+) AND cc.account_type IN ('Liability','Asset') GROUP BY src.user_je_source_name, gsb.NAME, jel.effective_date, jeh.NAME, jel.reference_1, jel.reference_5, jel.subledger_doc_sequence_value, jeh.doc_sequence_value, cc.gl_seg2, cc.gl_name_seg2, cc.gl_seg1, cc.GL_NAME_SEG1, jel.INVOICE_DATE UNION ALL SELECT 'Not in GL Yet' SOURCE, GLB.NAME sob_name, aph.accounting_date, pov.vendor_id, pov.vendor_name, api.invoice_id, api.invoice_num, api.doc_sequence_value, lia.gl_seg2 ACCOUNT, lia.gl_name_seg2 account_desc, lia.gl_seg1 Entity, lia.GL_NAME_SEG1 Entity_desc, api.INVOICE_DATE, SUM (NVL (accounted_dr, 0)) - SUM (NVL (accounted_cr, 0)) balance FROM ap_ae_lines_all apl, ap_ae_headers_all aph, gl_sets_of_books GLB, po_vendors pov, ap_invoices_all api, disc_ccid_dsc_mv lia WHERE apl.ae_line_type_code IN ('LIABILITY') AND apl.gl_sl_link_id IS NULL AND apl.ae_header_id = aph.ae_header_id AND apl.third_party_id = pov.vendor_id AND apl.reference2 = api.invoice_id AND pov.vendor_id = api.vendor_id AND apl.code_combination_id = lia.code_combination_id AND aph.set_of_books_id = GLB.set_of_books_id AND lia.account_type IN ('Liability','Asset') GROUP BY GLB.NAME, aph.accounting_date, pov.vendor_id, pov.vendor_name, api.doc_sequence_value, api.invoice_id, api.invoice_num, lia.gl_seg2, lia.gl_name_seg2, lia.gl_seg1, lia.gl_name_seg1, api.INVOICE_DATE / AP INVOICE DETAILS SELECT disc_gl_set_of_books.set_of_books_id, disc_gl_set_of_books.description AS set_of_books, disc_gl_set_of_books.set_of_book_currency AS set_of_book_currency, disc_ccid_dsc_mv.gl_seg1, disc_ccid_dsc_mv.gl_seg2, disc_ccid_dsc_mv.gl_seg3, disc_ccid_dsc_mv.gl_seg4, disc_ccid_dsc_mv.gl_seg5, disc_ccid_dsc_mv.gl_seg6, disc_ccid_dsc_mv.gl_seg7, disc_ccid_dsc_mv.gl_seg8, disc_ccid_dsc_mv.gl_seg9, disc_ccid_dsc_mv.gl_seg10, disc_ccid_dsc_mv.gl_seg11, disc_ccid_dsc_mv.gl_name_seg1, disc_ccid_dsc_mv.gl_name_seg2, disc_ccid_dsc_mv.gl_name_seg3, disc_ccid_dsc_mv.gl_name_seg4, disc_ccid_dsc_mv.gl_name_seg5, disc_ccid_dsc_mv.gl_name_seg6, disc_ccid_dsc_mv.gl_name_seg7, disc_ccid_dsc_mv.gl_name_seg8, disc_ccid_dsc_mv.gl_name_seg9, disc_ccid_dsc_mv.gl_name_seg10, disc_ccid_dsc_mv.gl_name_seg11, disc_ccid_dsc_mv1.gl_seg2 as liab_gl_seg2, disc_ccid_dsc_mv1.gl_name_seg2 as liab_gl_name_seg2, ap_invoices_all.invoice_num, ap_invoices_all.invoice_date, INITCAP (ap_invoices_all.invoice_type_lookup_code ) invoice_type_lookup_code, --AP_INVOICES_ALL.INVOICE_AMOUNT, ap_invoices_all.invoice_currency_code, ap_invoices_all.payment_currency_code, --AP_INVOICES_ALL.DESCRIPTION, --AP_INVOICES_ALL.EXCHANGE_RATE, ap_invoices_all.vendor_id, ap_invoices_all.vendor_site_id, po_vendor_sites_all.vendor_site_code, ap_invoice_distributions_all.accounting_date, ap_invoice_distributions_all.description, ap_invoice_distributions_all.amount, ap_invoice_distributions_all.base_amount, ap_invoice_distributions_all.period_name, ap_invoice_distributions_all.exchange_rate, ap_invoice_distributions_all.attribute4, --AP_INVOICES_PKG.GET_POSTING_STATUS(AP_INVOICES_ALL.INVOICE_ID) POSTING_FLAG , ap_invoices_pkg.get_approval_status (ap_invoices_all.invoice_id, ap_invoices_all.invoice_amount, ap_invoices_all.payment_status_flag, ap_invoices_all.invoice_type_lookup_code ) approval_status_lookup_code, --AP_INVOICES_PKG.SELECTED_FOR_PAYMENT_FLAG(AP_INVOICES_ALL.INVOICE_ID) SELECTED_FOR_PAYMENT_FLAG , --AP_INVOICES_PKG.GET_UNPOSTED_VOID_PAYMENT (AP_INVOICES_ALL.INVOICE_ID) UNPOSTED_VOID_PAYMENT_FLAG , --AP_INVOICES_PKG.GET_DISCOUNT_PAY_DISTS_FLAG (AP_INVOICES_ALL.INVOICE_ID) DISCOUNT_PAY_DISTS_FLAG , --AP_INVOICES_PKG.GET_PREPAYMENTS_APPLIED_FLAG (AP_INVOICES_ALL.INVOICE_ID) PREPAYMENTS_APPLIED_FLAG , --AP_INVOICES_PKG.GET_PAYMENTS_EXIST_FLAG (AP_INVOICES_ALL.INVOICE_ID) PAYMENTS_EXIST_FLAG , --DECODE(AP_INVOICES_ALL.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', AP_INVOICES_PKG.GET_PREPAY_AMOUNT_APPLIED (AP_INVOICES_ALL.INVOICE_ID), 0) PREPAY_AMOUNT_APPLIED , --AP_INVOICES_ALL.APPROVAL_STATUS APPROVAL_STATUS , ap_invoices_all.posting_status posting_status, ap_invoices_pkg.get_po_number (ap_invoices_all.invoice_id) po_number, ap_invoices_pkg.get_release_number (ap_invoices_all.invoice_id) release_number, ap_invoices_pkg.get_receipt_number (ap_invoices_all.invoice_id) receipt_number, ap_invoices_pkg.get_amount_withheld (ap_invoices_all.invoice_id) amount_withheld, ap_invoices_pkg.get_prepaid_amount (ap_invoices_all.invoice_id) prepaid_amount, alc4.displayed_field wfapproval_status_dsp, ap_invoices_all.approved_amount, ap_invoices_all.amount_paid, --ap_invoices_all.description, ap_invoices_all.tax_amount, po_vendors.vendor_name, INITCAP (NVL (po_vendors.vendor_type_lookup_code, 'VENDOR') ) vendor_type_lookup_code, ja_in_ap_tds_invoices.invoice_amount, ja_in_ap_tds_invoices.dm_invoice_num, ja_in_ap_tds_invoices.tds_invoice_num, ja_in_ap_tds_invoices.tds_tax_id, ja_in_ap_tds_invoices.tds_section, ja_in_ap_tds_invoices.tds_tax_rate, ja_in_ap_tds_invoices.tds_amount, ap_invoice_distributions_all.task_id, ap_invoice_distributions_all.expenditure_organization_id, ap_invoices_all.invoice_id, ap_invoices_all.doc_sequence_id doc_sequence_id, ap_invoices_all.doc_sequence_value doc_sequence_value, disc_prepay_invoice_details.prepay_amount_applied, disc_prepay_invoice_details.prepayment_invoice_num FROM ap.ap_invoices_all ap_invoices_all, ap.ap_invoice_distributions_all ap_invoice_distributions_all, apps.disc_gl_set_of_books disc_gl_set_of_books, apps.disc_ccid_dsc_mv disc_ccid_dsc_mv, po.po_vendors po_vendors, po.po_vendor_sites_all po_vendor_sites_all, ja.ja_in_ap_tds_invoices ja_in_ap_tds_invoices, disc_prepay_invoice_details disc_prepay_invoice_details, --AP_LOOKUP_CODES ALC1, --AP_LOOKUP_CODES ALC2, --AP_LOOKUP_CODES ALC3, ap_lookup_codes alc4, apps.disc_ccid_dsc_mv disc_ccid_dsc_mv1 WHERE ap_invoice_distributions_all.invoice_id = ap_invoices_all.invoice_id AND ap_invoice_distributions_all.set_of_books_id = disc_gl_set_of_books.set_of_books_id AND ap_invoice_distributions_all.dist_code_combination_id = disc_ccid_dsc_mv.code_combination_id AND ap_invoice_distributions_all.reversal_flag IS NULL AND po_vendors.vendor_id = ap_invoices_all.vendor_id AND ap_invoices_all.vendor_site_id = po_vendor_sites_all.vendor_site_id AND ap_invoice_distributions_all.invoice_id = ja_in_ap_tds_invoices.invoice_id(+) AND po_vendors.vendor_id = po_vendor_sites_all.vendor_id AND alc4.lookup_type(+) = 'AP_WFAPPROVAL_STATUS' AND alc4.lookup_code(+) = ap_invoices_all.wfapproval_status AND ap_invoice_distributions_all.invoice_distribution_id = disc_prepay_invoice_details.invoice_distribution_id(+) and ap_invoices_all.ACCTS_PAY_CODE_COMBINATION_ID = disc_ccid_dsc_mv1.code_combination_id (+) / PA PROJECT TASKS SELECT PPA.SEGMENT1, PPA.NAME, PT.TASK_NUMBER, PT.TASK_NAME, PT.ATTRIBUTE1 FROM PA_PROJECTS_ALL PPA, PA_TASKS PT WHERE PPA.PROJECT_ID = PT.PROJECT_ID AND PT.BILLABLE_FLAG='Y' AND PPA.TEMPLATE_FLAG='N' AND PT.TOP_TASK_ID <> PT.TASK_ID AND UPPER(PPA.PROJECT_TYPE)='PROGRAM-US' / PRE-PAY STATUS QUERY SELECT aid.accounting_date prepay_accounting_date, NVL (aid1.accounting_date, aid.accounting_date ) prepay_application_date, gsb.short_name sob_name, pv.vendor_name vendor_name, pvs.vendor_site_code vendor_site_code, aia.invoice_num invoice_num, aia.invoice_date invoice_date, aip.accounting_date payment_date, gcc.segment2 prepay_natural_account, aia.description prepay_description, aia.invoice_currency_code currency, aid.invoice_distribution_id prepay_distribution_id, aid.amount currency_amt, NVL (aid.base_amount, aid.amount) inr_amount, NVL (SUM (-1 * aid1.amount), 0) currency_amt_applied, NVL (SUM (-1 * NVL (aid1.base_amount, aid1.amount)), 0 ) inr_amount_applied, aid.amount + NVL (SUM (aid1.amount), 0) available_currency_amount, NVL (aid.base_amount, aid.amount) + NVL (SUM (NVL (aid1.base_amount, aid1.amount)), 0) available_inr_amount FROM ap_invoices_all aia, ap_invoice_distributions_all aid, ap_invoice_distributions_all aid1, po_vendors pv, po_vendor_sites_all pvs, gl_code_combinations gcc, ap_invoice_payments_all aip, gl_sets_of_books gsb WHERE aia.invoice_id = aid.invoice_id AND aia.invoice_type_lookup_code = 'PREPAYMENT' AND aia.invoice_amount = aia.amount_paid AND aid.invoice_distribution_id = aid1.prepay_distribution_id(+) AND aia.vendor_id = pv.vendor_id AND aia.vendor_site_id = pvs.vendor_site_id AND pv.vendor_id = pvs.vendor_id AND aid.dist_code_combination_id = gcc.code_combination_id AND aip.invoice_id = aid.invoice_id AND gsb.set_of_books_id = aia.set_of_books_id and aip.REVERSAL_FLAG= 'N' --AND DECODE(APPS.FND_GLOBAL.RESP_ID,21612,1,APPS.FND_GLOBAL.RESP_ID) = DECODE(APPS.FND_GLOBAL.RESP_ID,21612,1,DISC_SECURITY.RESP_ID) --AND DECODE(APPS.FND_GLOBAL.RESP_ID,21612,1,DISC_SECURITY.GL_SOB_ID) = DECODE(APPS.FND_GLOBAL.RESP_ID,21612,1,GSB.SET_OF_BOOKS_ID) GROUP BY gsb.short_name, aia.invoice_num, aia.invoice_date, aip.accounting_date, gcc.segment2, aia.description, aia.invoice_currency_code, aid.invoice_distribution_id, aid.amount, aid.base_amount, pv.vendor_name, pvs.vendor_site_code, aid.accounting_date, aid1.accounting_date / PA PROJECT MASTER SELECT ppa.project_id, ppa.NAME "PROJECT NAME", ppa.long_name "PROJECT ALIAS", ppa.description "PROJECT DESCRIPTION", ppa.start_date "START DATE", ppa.completion_date "END DATE", prc.customer_name "PRIMARY CUSTOMER", prc1.customer_name "SECONDARY CUSTOMER", hou.NAME "BUSINESS UNIT", LOB.class_code "LINE OF BUSINESS", pra.class_code "PRACTICE", prloc.class_code "PROGRAM LOCATION", so.class_code "SERVICE OFFERING", bm.class_code "BUSINESS MODEL", sb.class_code "SETUP BILLING", 0 "PARENT PROJECT ID", ppa.segment1 "PROJECT NUMBER", '0' "TASK NUMBER", ppa.project_type "PROJECT TYPE", pm."PROJECT MANAGER", pgm."PROGRAM MANAGER", vpp."VICE PRESIDENT", prc.project_relationship_code "PARENT RELATIONSHIP" FROM pa_projects_all ppa, hr_all_organization_units hou, pa_project_customers_v prc, disc_pa_sec_customers prc1, pa_project_classes_v LOB, pa_project_classes_v pra, pa_project_classes_v prloc, pa_project_classes_v so, pa_project_classes_v sb, disc_pa_biz_model bm, --PA_PROJECT_CLASSES_V BIL, disc_pa_pm pm, disc_pa_pgm pgm, disc_pa_vpp vpp WHERE ppa.carrying_out_organization_id = hou.organization_id AND ppa.template_flag != 'Y' AND ppa.project_id = prc.project_id(+) AND ppa.project_id = prc1.project_id(+) AND ppa.project_id = LOB.project_id(+) AND LOB.class_category = 'LINE OF BUSINESS' AND ppa.project_id = pra.project_id(+) AND pra.class_category = 'PRACTICE' AND ppa.project_id = prloc.project_id(+) AND prloc.class_category = 'PROGRAM LOCATION' AND ppa.project_id = so.project_id(+) AND so.class_category = 'SERVICE OFFERING' AND ppa.project_id = sb.project_id(+) AND sb.class_category = 'SETUP BILLING' AND ppa.project_id = bm.project_id(+) --AND BM.CLASS_CATEGORY = 'BUSINESS MODEL' --AND PPA.PROJECT_ID = BIL.PROJECT_ID (+) AND BIL.CLASS_CATEGORY (+) = --'FINAL PROJECTS FOR GO LIVE' AND BIL.CLASS_CODE = 'YES' AND ppa.project_id = pm.project_id(+) -- AND UPPER (prm.ROLE) = 'PROJECT MANAGER' --AND UPPER(PPA.PROJECT_TYPE) LIKE 'PROGRAM%' --AND PRC.PROJECT_RELATIONSHIP_CODE IN ('PARENT','INTERNAL'); AND ppa.project_id = pgm.project_id(+) -- AND UPPER (pm.ROLE) = 'PROGRAM MANAGER' AND ppa.project_id = vpp.project_id(+) -- AND UPPER (vpp.ROLE) = 'VICE PRESIDENT-PROGRAM' -- and ppa.segment1 = '10242' / PA PROJECT PROGRAM MAP SELECT PPA1.PROJECT_ID, PPA1.SEGMENT1 "ACCOUNT_VALUE", PPA2.SEGMENT1 "INDIAN PROJECTS" FROM PA_TASKS PT, PA_PROJECTS_ALL PPA1,PA_PROJECTS_ALL PPA2, PA_PROJECT_CUSTOMERS PPC ,PA_PROJECT_TYPES_ALL PPT WHERE PT.TASK_ID = PPC.RECEIVER_TASK_ID AND PT.PROJECT_ID = PPA1.PROJECT_ID AND PPT.PROJECT_TYPE = PPA2.PROJECT_TYPE AND PPC.PROJECT_ID=PPA2.PROJECT_ID AND PPT.PROJECT_TYPE_CLASS_CODE = 'CONTRACT' AND PPA2.PROJECT_STATUS_CODE ='APPROVED' AND PPA1.TEMPLATE_FLAG='N' UNION SELECT PPA.PROJECT_ID, PPA.SEGMENT1, PPA.SEGMENT1 FROM PA_PROJECTS_ALL PPA, PA_PROJECT_TYPES_ALL PPT WHERE PPT.PROJECT_TYPE = PPA.PROJECT_TYPE AND PPT.PROJECT_TYPE_CLASS_CODE <> 'CONTRACT' AND PPA.TEMPLATE_FLAG='N' UNION SELECT PPA1.PROJECT_ID, PPA1.SEGMENT1 "ACCOUNT_VALUE", PPA1.SEGMENT1 "INDIAN PROJECTS" FROM PA_PROJECTS_ALL PPA1, PA_PROJECT_CUSTOMERS PPC ,PA_PROJECT_TYPES_ALL PPT WHERE PPT.PROJECT_TYPE = PPA1.PROJECT_TYPE AND PPC.PROJECT_ID=PPA1.PROJECT_ID AND PPT.PROJECT_TYPE_CLASS_CODE = 'CONTRACT' AND PPC.BILL_ANOTHER_PROJECT_FLAG='N' AND PPA1.PROJECT_STATUS_CODE ='APPROVED' AND PPA1.TEMPLATE_FLAG='N' / GL BILL COLLECTION & RECOVERY SELECT a_b_flag, set_of_books_id, set_of_books, set_of_book_currency, gl_seg1, gl_seg2, gl_seg3, gl_seg4, gl_seg5, gl_seg6, gl_seg7, gl_seg8, gl_seg9, gl_seg10, gl_seg11, item_date, gl_name_seg1, gl_name_seg2, gl_name_seg3, gl_name_seg4, gl_name_seg5, gl_name_seg6, gl_name_seg7, gl_name_seg8, gl_name_seg9, gl_name_seg10, gl_name_seg11, txn_no AS invoice_no, trx_date AS invoice_date, cm_no, NULL AS receipt_no, NULL AS receipt_date, period_name AS bill_period_name, NULL AS coll_period_name, NULL AS rev_period_name, customer_name, period_net AS billing, 0 AS collection, 0 AS revenues, 0 AS exchange FROM disc_gl_billing UNION ALL SELECT a_b_flag, set_of_books_id, set_of_books, set_of_book_currency, gl_seg1, gl_seg2, gl_seg3, gl_seg4, gl_seg5, gl_seg6, gl_seg7, gl_seg8, gl_seg9, gl_seg10, gl_seg11, item_date, gl_name_seg1, gl_name_seg2, gl_name_seg3, gl_name_seg4, gl_name_seg5, gl_name_seg6, gl_name_seg7, gl_name_seg8, gl_name_seg9, gl_name_seg10, gl_name_seg11, txn_no AS invoice_no, trx_date AS invoice_date, cm_no, NULL AS receipt_date, receipt_date, NULL AS bill_period_name, period_name AS coll_period_name, NULL AS rev_period_name, customer_name, 0 AS billing, period_net AS collection, 0 AS revenues, 0 AS exchange FROM disc_gl_collection UNION ALL SELECT a_b_flag, set_of_books_id, set_of_books, set_of_book_currency, gl_seg1, gl_seg2, gl_seg3, gl_seg4, gl_seg5, gl_seg6, gl_seg7, gl_seg8, gl_seg9, gl_seg10, gl_seg11, item_date, gl_name_seg1, gl_name_seg2, gl_name_seg3, gl_name_seg4, gl_name_seg5, gl_name_seg6, gl_name_seg7, gl_name_seg8, gl_name_seg9, gl_name_seg10, gl_name_seg11, txn_no AS invoice_no, trx_date AS invoice_date, cm_no, NULL AS receipt_date, receipt_date, NULL AS bill_period_name, period_name AS coll_period_name, NULL AS rev_period_name, customer_name, 0 AS billing, 0 AS collection, 0 AS revenues, period_net AS exchange FROM disc_gl_exchange / HR EMPLOYEES SELECT ppf.full_name "Employee_Name", ppf.employee_number "Employee_Num", ppf.date_of_birth "Date_Of_Birth", ppf.start_date "Date_Of_Joining", ppf.email_address "Email_Address", ppf.attribute1 "Employee_Type", psf.full_name "Supervisor", hou.NAME, paf.effective_start_date start_date, paf.effective_end_date end_date, (paf.effective_end_date - paf.effective_start_date) days_employed FROM per_all_people_f ppf, per_all_assignments_f paf, per_all_people_f psf, hr_all_organization_units hou WHERE ppf.person_id = paf.person_id(+) AND paf.supervisor_id = psf.person_id(+) --and ppf.person_type_id = 6 --and psf.person_type_id = 6 AND paf.primary_flag = 'Y' AND paf.organization_id = hou.organization_id / ACCOUNT PARENT & CHILD SELECT DISTINCT b.parent_flex_value, b.child_flex_value_low, b.child_flex_value_high, c.description, d.segment, d. description as account FROM fnd_flex_values_vl a, fnd_flex_value_norm_hierarchy b, disc_segment2 c, disc_segment2 D WHERE a.flex_value_set_id = '1008011' AND a.flex_value_set_id = b.flex_value_set_id AND c.SEGMENT = b.parent_flex_value and b.RANGE_ATTRIBUTE = 'C' and D.segment between b.child_flex_value_low and b.child_flex_value_high /
0 comments:
Post a Comment