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
/
No comments:
Post a Comment
Please review my topic and update your comments
Note: only a member of this blog may post a comment.