ORACLE APPS ALL QUERIES

ORACLE APPS ALL QUERIES

ALL QUERIES 1

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

/