************************
Invoice Tolerance Setup
************************
SELECT
TOLERANCE_NAME,
DESCRIPTION,
TOLERANCE_TYPE,
PRICE_TOLERANCE,
QUANTITY_TOLERANCE,
QTY_RECEIVED_TOLERANCE,
MAX_QTY_ORD_TOLERANCE,
MAX_QTY_REC_TOLERANCE,
SHIP_AMT_TOLERANCE,
RATE_AMT_TOLERANCE,
TOTAL_AMT_TOLERANCE,
QTY_CONSUMED_TOLERANCE,
MAX_QTY_CON_TOLERANCE
FROM
AP_TOLERANCE_TEMPLATES
************************************
Invoice Accounting Information
*************************************
SELECT aia.INVOICE_ID "Invoice Id",
aia.INVOICE_NUM "Invoice Number",
aia.INVOICE_DATE "Invoice Date",
aia.INVOICE_AMOUNT "Amount",
xal.ENTERED_DR "Entered DR in SLA",
xal.ENTERED_CR "Entered CR in SLA",
xal.ACCOUNTED_DR "Accounted DR in SLA",
xal.ACCOUNTED_CR "Accounted CR in SLA",
gjl.ENTERED_DR "Entered DR in GL",
gjl.ACCOUNTED_DR "Accounted DR in GL",
xal.ACCOUNTING_CLASS_CODE "Accounting Class",
aia.INVOICE_CURRENCY_CODE "Inv Curr Code",
aia.PAYMENT_CURRENCY_CODE "Pay Curr Code",
aia.GL_DATE "GL Date",
xah.PERIOD_NAME "Period",
aia.PAYMENT_METHOD_CODE "Payment Method",
xah.JE_CATEGORY_NAME "JE Category Name",
AIDA.CANCELLATION_FLAG
FROM ap_invoices_all aia,
AP_INVOICE_LINES_ALL AILA,
AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
xla_transaction_entities XTE,
xla_events xev,
xla_ae_headers XAH,
xla_ae_lines XAL,
GL_IMPORT_REFERENCES gir,
gl_je_headers gjh,
gl_je_lines gjl,
gl_code_combinations gcc
WHERE aia.INVOICE_ID = xte.source_id_int_1
AND xev.entity_id = xte.entity_id
AND xah.entity_id = xte.entity_id
AND xah.event_id = xev.event_id
AND XAH.ae_header_id = XAL.ae_header_id
AND XAH.gl_transfer_status_code= 'Y'
AND XAL.GL_SL_LINK_ID =gir.GL_SL_LINK_ID
AND gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE
AND gjl.JE_HEADER_ID =gjh.JE_HEADER_ID
AND gjh.JE_HEADER_ID =gir.JE_HEADER_ID
AND gjl.JE_HEADER_ID =gir.JE_HEADER_ID
AND gir.JE_LINE_NUM =gjl.JE_LINE_NUM
AND gcc.CODE_COMBINATION_ID =XAL.CODE_COMBINATION_ID
AND gcc.CODE_COMBINATION_ID =gjl.CODE_COMBINATION_ID
and AIA.INVOICE_ID = AILA.INVOICE_ID
AND AILA.INVOICE_ID = AIDA.INVOICE_ID
AND AILA.LINE_NUMBER = AIDA.INVOICE_LINE_NUMBER
AND gjh.STATUS ='P'
AND gjh.Actual_flag ='A'
--AND gjh.CURRENCY_CODE ='USD'
AND aia.Invoice_num = '.LB1974CAN'
and xah.APPLICATION_ID = 200
--AND XAH.je_category_name = &p_je_category_name
*********************************
Expense Report Basic INFORMATION
*********************************
select
a.EXPENSE_REPORT_NUM,
a.EXPENSE_REPORT_ID,
a.EXPENSE_STATUS_CODE,
--a.ORG_ID,
--a.PERSON_ID,
--a.ASSIGNMENT_ID,
a.last_update_date,
b. NAME BUSINESS_UNIT,
c.VALIDATION_STATUS_CODE,
c.REIMBURSABLE_AMOUNT,
c.DESCRIPTION,
c.START_DATE,
c.MERCHANT_NAME,
c.DAILY_DISTANCE,
c.TRIP_DISTANCE,
c.TAX_CLASSIFICATION_CODE,
c.expense_type_category_code,
--c.sequence_num,
e.name expense_type,
d.PJC_USER_DEF_ATTRIBUTE1,
g.concatenated_segments,
d.REIMBURSABLE_AMOUNT dist_amt
from
exm_expense_reports a,
HR_ALL_ORGANIZATION_UNITS b,
EXM_EXPENSES c,
EXM_EXPENSE_DISTS d,
EXM_EXPENSE_TYPES e,
gl_code_combinations g
where
a.ORG_ID = b.ORGANIZATION_ID
and a.EXPENSE_REPORT_ID = c.EXPENSE_REPORT_ID
and a.EXPENSE_REPORT_ID = d.EXPENSE_REPORT_ID
and c.EXPENSE_REPORT_ID = d.EXPENSE_REPORT_ID
and c.EXPENSE_ID = d.EXPENSE_ID
and d.code_combination_id = g.code_combination_id
and e.EXPENSE_TYPE_ID = c.EXPENSE_TYPE_ID
and EXPENSE_REPORT_NUM in ( 'ER010941475020','ER010941475054')
order by 1,d.EXPENSE_ID
Expense Report Table
select
e.EXPENSE_REPORT_NUM,
e.EXPENSE_REPORT_ID,
e.EXPENSE_STATUS_CODE,
e.ORG_ID,
e.PERSON_ID,
e.ASSIGNMENT_ID,
e.last_update_date
from
exm_expense_reports e,
FND_LOOKUP_VALUES_VL FLV1
where
EXPENSE_STATUS_CODE=FLV1.MEANING
and FLV1.LOOKUP_TYPE='WSP_EXM_ GSS_AUDITSTATUS'
SELECT DISTINCT
1 AS KEY,
PPNF.DISPLAY_NAME Employee_Name,
EER.EXPENSE_REPORT_NUM,
HAO.NAME BUSINESS_UNIT,
PAPF.PERSON_NUMBER Employee_Number,
SUBSTR(PD.NAME,1,8) Employee_COST_CENTRE,
paam.assignment_type,
PAAM.ASSIGNMENT_STATUS_TYPE
FROM
EER,--LKP,
--EXM_EXP_REP_PROCESSING EERP,
HR_ALL_ORGANIZATION_UNITS HAO,
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF,
PER_ALL_ASSIGNMENTS_M PAAM,
PER_DEPARTMENTS PD
WHERE
1=1
--AND EERP.EVENT='APPROVE'
--AND EER.EXPENSE_REPORT_ID = EERP.EXPENSE_REPORT_ID
--AND EER.EXPENSE_STATUS_CODE = EERP.EXPENSE_STATUS_CODE
AND HAO.NAME in (select MEANING from LKP where LOOKUP_TYPE='WSP_EXM_ GSS_AUDITBU')
AND EER.ORG_ID = HAO.ORGANIZATION_ID
AND SYSDATE BETWEEN HAO.EFFECTIVE_START_DATE and HAO.EFFECTIVE_END_DATE
AND EER.PERSON_ID = PAPF.PERSON_ID
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE and PAPF.EFFECTIVE_END_DATE
AND PAPF.PERSON_ID = PPNF.PERSON_ID
AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE and PPNF.EFFECTIVE_END_DATE
AND PPNF.NAME_TYPE= 'GLOBAL'
AND PAPF.PERSON_ID = PAAM.PERSON_ID
AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE and PAAM.EFFECTIVE_END_DATE
AND PAAM.PRIMARY_FLAG = 'Y'
AND PAAM.PRIMARY_ASSIGNMENT_FLAG = 'Y'
AND PAAM.ASSIGNMENT_STATUS_TYPE in (select MEANING from LKP where LOOKUP_TYPE='WSP_EXM_ GSS_ASNGTYPE')
AND PD.ORGANIZATION_ID = PAAM.ORGANIZATION_ID
AND SYSDATE BETWEEN PD.EFFECTIVE_START_DATE and PD.EFFECTIVE_END_DATE
AND EER.ASSIGNMENT_ID=PAAM.ASSIGNMENT_ID
and EER.EXPENSE_REPORT_NUM='ER010104856219-2'
AND (EER.last_update_date1>= NVL(:P_DATE_FROM,(SELECT nvl(MAX(erh.scheduled),sysdate-1)
FROM fusion.ess_request_history erh,
fusion.ess_request_property erp_name
WHERE 1 = 1
AND erh.requestid = erp_name.requestid
AND erh.SUBMITTER = SYS_CONTEXT( 'USERENV', 'CLIENT_IDENTIFIER' )
AND erp_name.value = '/Custom/Financials/GSS/Expense Audit Report.xdo'
AND erh.state IN( 12,17)))
AND EER.last_update_date1 <=NVL(:P_DATE_TO,EER.last_update_date1))
***************************
SUPPLIER BASIC INFORMATION
***************************
SELECT
POS.SEGMENT1 AS SUPPLIER_NUMBER,
HP.PARTY_NAME,
PSSA.VENDOR_SITE_CODE,
TO_CHAR(PSSA.EFFECTIVE_START_DATE,'MM/DD/YYYY') SITE_START_DATE,
TO_CHAR(PSSA.EFFECTIVE_END_DATE,'MM/DD/YYYY') SITE_END_DATE,
PSSA.PURCHASING_SITE_FLAG,
PSSA.RFQ_ONLY_SITE_FLAG,
PSSA.PAY_SITE_FLAG,
PSSA.TERMS_ID,
ATL.NAME,
ATL.DESCRIPTION,
PSSA.INVOICE_CURRENCY_CODE,
PSSA.PAYMENT_CURRENCY_CODE,
PSSA.HOLD_ALL_PAYMENTS_FLAG,
PSSA.HOLD_FUTURE_PAYMENTS_FLAG,
PSSA.HOLD_REASON,
PSSA.HOLD_UNMATCHED_INVOICES_FLAG,
PSSA.PAYMENT_HOLD_DATE,
PSSA.PRC_BU_ID,
FABU.BU_NAME,
PSSA.PAY_GROUP_LOOKUP_CODE
FROM
POZ_SUPPLIERS POS,
HZ_PARTIES HP,
POZ_SUPPLIER_SITES_ALL_M PSSA,
AP_TERMS_TL ATL,
FUN_ALL_BUSINESS_UNITS_V FABU
WHERE
POS.PARTY_ID = HP.PARTY_ID
AND POS.VENDOR_ID = PSSA.VENDOR_ID
AND PSSA.TERMS_ID = ATL.TERM_ID
AND ATL.LANGUAGE = 'US'
AND PSSA.PRC_BU_ID = FABU.BU_ID
AND (POS.SEGMENT1 = :SUPPLIER_NUMBER OR :SUPPLIER_NUMBER IS NULL)
************************
AP INVOICE DETAILS
************************
SELECT
AIA.ORG_ID,
FABU.BU_NAME,
AIA.INVOICE_NUM,
TO_CHAR(AIA.INVOICE_DATE,'MM/DD/YYYY') INVOICE_DT,
AIA.SOURCE,
AIA.INVOICE_TYPE_LOOKUP_CODE,
AIA.PAY_GROUP_LOOKUP_CODE,
POS.SEGMENT1 AS SUPPLIER_NUMBER,
HP.PARTY_NAME,
PSSA.VENDOR_SITE_CODE,
AIA.INVOICE_CURRENCY_CODE,
AIA.PAYMENT_CURRENCY_CODE,
AIA.INVOICE_AMOUNT,
AIA.AMOUNT_PAID,
AIA.TERMS_ID,
ATL.NAME,
ATL.DESCRIPTION,
TO_CHAR(AIA.TERMS_DATE,'MM/DD/YYYY') INV_TERMS_DATE,
AIA.WFAPPROVAL_STATUS,
AIA.LEGAL_ENTITY_ID,
XEP.NAME LE_NAME,
AIA.PAYMENT_METHOD_CODE,
AIA.INTERCOMPANY_FLAG,
AIA.EXTERNAL_BANK_ACCOUNT_ID
FROM
AP_INVOICES_ALL AIA,
POZ_SUPPLIERS POS,
HZ_PARTIES HP,
POZ_SUPPLIER_SITES_ALL_M PSSA,
AP_TERMS_TL ATL,
FUN_ALL_BUSINESS_UNITS_V FABU,
XLE_ENTITY_PROFILES XEP
WHERE
POS.PARTY_ID = HP.PARTY_ID
AND POS.VENDOR_ID = PSSA.VENDOR_ID
AND AIA.VENDOR_ID = POS.VENDOR_ID
AND AIA.VENDOR_SITE_ID = PSSA.VENDOR_SITE_ID
AND AIA.TERMS_ID = ATL.TERM_ID
AND ATL.LANGUAGE = 'US'
AND AIA.ORG_ID = FABU.BU_ID
AND (AIA.INVOICE_NUM = :INV_NO OR :INV_NO IS NULL)
AND AIA.LEGAL_ENTITY_ID = XEP.LEGAL_ENTITY_ID
************************
EXPENSE AP INVOICE DETAILS
************************
SELECT
AIA.ORG_ID,
FABU.BU_NAME,
AIA.INVOICE_NUM,
TO_CHAR(AIA.INVOICE_DATE,'MM/DD/YYYY') INVOICE_DT,
AIA.SOURCE,
AIA.INVOICE_TYPE_LOOKUP_CODE,
AIA.PAY_GROUP_LOOKUP_CODE,
AIA.INVOICE_CURRENCY_CODE,
AIA.PAYMENT_CURRENCY_CODE,
AIA.INVOICE_AMOUNT,
AIA.AMOUNT_PAID,
AIA.TERMS_ID,
ATL.NAME,
ATL.DESCRIPTION,
TO_CHAR(AIA.TERMS_DATE,'MM/DD/YYYY') INV_TERMS_DATE,
AIA.WFAPPROVAL_STATUS,
AIA.LEGAL_ENTITY_ID,
XEP.NAME LE_NAME,
AIA.PAYMENT_METHOD_CODE,
AIA.INTERCOMPANY_FLAG,
AIA.EXTERNAL_BANK_ACCOUNT_ID,
PPF.FULL_NAME,
AER.PERSON_ID
FROM
EXM_EXPENSE_REPORTS AER,
AP_INVOICES_ALL AIA,
AP_TERMS_TL ATL,
FUN_ALL_BUSINESS_UNITS_V FABU,
XLE_ENTITY_PROFILES XEP,
PER_PERSON_NAMES_F PPF
WHERE
AIA.REFERENCE_KEY1 = TO_CHAR(AER.EXPENSE_REPORT_ID)
AND AIA.TERMS_ID = ATL.TERM_ID
AND ATL.LANGUAGE = 'US'
AND AIA.ORG_ID = FABU.BU_ID
--AND (AIA.INVOICE_NUM = :INV_NO OR :INV_NO IS NULL)
AND AIA.LEGAL_ENTITY_ID = XEP.LEGAL_ENTITY_ID
AND AER.EXPENSE_REPORT_NUM = :EXP_NO
AND PPF.PERSON_ID=AER.PERSON_ID
AND PPF.NAME_TYPE = 'GLOBAL'
*********************************
CREDIT CARD TRANSACTION DETAILS
*********************************
SELECT
TRUNC(SYSDATE - E.START_DATE) "NO. OF DAYS",
PAPF.PERSON_NUMBER EMPLOYEE_NUMBER,
HZP.PERSON_FIRST_NAME EMP_FIRST_NAME,
HZP.PERSON_LAST_NAME EMP_LAST_NAME,
E.ASSIGNMENT_ID,
ECP.CARD_PROGRAM_NAME AS CARD_PROGRAM_NAME,
CC.CARD_PROGRAM_ID AS CARD_PROG_ID,
E.ORG_ID AS ORG_ID,
E.EMP_DEFAULT_COST_CENTER AS COST_CTR,
CC.COMPANY_ACCOUNT_ID AS COMPANY_ACCT_ID,
E.MERCHANT_NAME,
EC.CARD_REFERENCE_ID,
ICC.MASKED_CC_NUMBER CREDIT_CARD_NUM,
CC.DESCRIPTION DESCRIPTION,
E.EMP_DEFAULT_COST_CENTER AS COST_CENTER,
EED.SEGMENT1, EED.SEGMENT2, EED.SEGMENT3, EED.SEGMENT4, EED.SEGMENT5,
CC.BILLED_DATE,
CC.BILLED_AMOUNT,
CC.POSTED_DATE,
CC.POSTED_AMOUNT,
CC.TRANSACTION_DATE,
CC.TRANSACTION_AMOUNT,
E.ORIG_REIMBURSABLE_AMOUNT,
DECODE(ER.EXPENSE_STATUS_CODE, NULL, 'EMPLOYEE',
'PEND_MGR_APPROVAL', 'APPROVER',
'SUBMITTED', 'APPROVER',
'PEND_IND_APPROVAL', 'EMPLOYEE',
'MGR_REJECTED', 'EMPLOYEE',
'IND_REJECTED', 'EMPLOYEE',
'SAVED', 'EMPLOYEE',
'WITHDRAWN', 'EMPLOYEE',
'REQUEST_INFO', 'EMPLOYEE',
'EMPLOYEE') AS PENDING_ACTION,
CA.PAYMENT_CURRENCY_CODE AS CURRENCY_CODE
FROM FUSION.EXM_EXPENSES E,
FUSION.EXM_EXPENSE_REPORTS ER,
FUSION.EXM_CREDIT_CARD_TRXNS CC,
FUSION.EXM_CC_COMPANY_ACCOUNTS CA,
FUSION.EXM_CARDS EC,
FUSION.PER_ALL_PEOPLE_F PAPF,
FUSION.IBY_CREDITCARD ICC,
FUSION.HZ_PARTIES HZP,
FUSION.PER_USERS PU,
FUSION.EXM_EXPENSE_DISTS EED,
FUSION.EXM_CARD_PROGRAMS ECP
WHERE (E.EXPENSE_SOURCE = 'CREDIT_CARD' OR E.EXPENSE_SOURCE = 'BUSINESS_TRAVEL')
AND E.EXPENSE_REPORT_ID = ER.EXPENSE_REPORT_ID(+)
AND ER.EXPENSE_STATUS_CODE(+) NOT IN ('PAID', 'PARTIAL_PAID', 'APPROVAL_COMPLETE', 'INVOICED', 'INVOICE_CANCELED')
AND (E.EXPENSE_REPORT_ID IS NULL OR ER.EXPENSE_REPORT_ID IS NOT NULL)
AND (E.ITEMIZATION_PARENT_EXPENSE_ID IS NULL OR E.ITEMIZATION_PARENT_EXPENSE_ID = -1)
AND E.CREDIT_CARD_TRXN_ID = CC.CREDIT_CARD_TRXN_ID
AND CC.COMPANY_ACCOUNT_ID = CA.CC_COMPANY_ACCOUNT_ID(+)
AND EC.CARD_ID(+) = CC.CARD_ID
AND NVL(EC.ACCOUNT_TYPE_CODE,'EMPLOYEE') <> 'COMPANY'
AND PAPF.PERSON_ID = E.PERSON_ID
AND PU.PERSON_ID = E.PERSON_ID
AND EC.CARD_REFERENCE_ID = ICC.INSTRID
AND PU.USER_GUID = HZP.USER_GUID(+)
AND E.EXPENSE_ID = EED.EXPENSE_ID
-- AND PAPF.PERSON_NUMBER IN ( 1984678,1984999,1978237,1992954,1986569, 1984999 )
AND ECP.CARD_PROGRAM_ID = CC.CARD_PROGRAM_ID
AND HZP.PERSON_FIRST_NAME = 'ANNA'
AND HZP.PERSON_LAST_NAME ='ROBAK'
GROUP BY TRUNC(SYSDATE - CC.BILLED_DATE),
TRUNC(SYSDATE - E.START_DATE),
PAPF.PERSON_NUMBER,
HZP.PERSON_FIRST_NAME ,
HZP.PERSON_LAST_NAME ,
E.ASSIGNMENT_ID,
ECP.CARD_PROGRAM_NAME,
CC.CARD_PROGRAM_ID,
E.ORG_ID,
E.EMP_DEFAULT_COST_CENTER,
CC.COMPANY_ACCOUNT_ID,
E.MERCHANT_NAME,
EC.CARD_REFERENCE_ID,
ICC.MASKED_CC_NUMBER,
CC.DESCRIPTION,
E.EMP_DEFAULT_COST_CENTER,
EED.SEGMENT1, EED.SEGMENT2, EED.SEGMENT3, EED.SEGMENT4, EED.SEGMENT5,
CC.BILLED_DATE,
CC.BILLED_AMOUNT,
CC.POSTED_DATE,
CC.POSTED_AMOUNT,
CC.TRANSACTION_DATE,
CC.TRANSACTION_AMOUNT,
E.ORIG_REIMBURSABLE_AMOUNT,
DECODE(ER.EXPENSE_STATUS_CODE, NULL, 'EMPLOYEE',
'PEND_MGR_APPROVAL', 'APPROVER',
'SUBMITTED', 'APPROVER',
'PEND_IND_APPROVAL', 'EMPLOYEE',
'MGR_REJECTED', 'EMPLOYEE',
'IND_REJECTED', 'EMPLOYEE',
'SAVED', 'EMPLOYEE',
'WITHDRAWN', 'EMPLOYEE',
'REQUEST_INFO', 'EMPLOYEE',
'EMPLOYEE'),
CA.PAYMENT_CURRENCY_CODE
ORDER BY 1 DESC
*************************
Supplier and Site Details
*************************
SELECT
SUP.SEGMENT1 AS VENDOR_NO,
HZP.PARTY_NAME,
SUP.ENABLED_FLAG,
SUP.BUSINESS_RELATIONSHIP,
SUP.SUMMARY_FLAG,
SUP.VENDOR_TYPE_LOOKUP_CODE,
HZP.DUNS_NUMBER_C,
SUP.ONE_TIME_FLAG,
SUP.TYPE_1099,
SUP.FEDERAL_REPORTABLE_FLAG,
SUP.TAX_REPORTING_NAME,
HZAP.PARTY_NAME AS VENDOR_NAME_ALT,
HZPS.PARTY_SITE_NAME AS ADDRESS_NAME,
HZL.COUNTRY,
HZL.LOCATION_ID,
HZL.ADDRESS1,
HZL.ADDRESS2,
HZL.ADDRESS3,
HZL.ADDRESS4,
HZL.CITY,
HZL.COUNTY,
HZL.STATE,
HZL.PROVINCE,
HZL.POSTAL_CODE,
HZL.BUILDING,
HZL.FLOOR_NUMBER,
SUPSITE.VENDOR_SITE_CODE,
SUPSITE.PURCHASING_SITE_FLAG,
FABU.BU_NAME,
SUPSITE.RFQ_ONLY_SITE_FLAG,
SUPSITE.PAY_SITE_FLAG,
SUPSITE.PCARD_SITE_FLAG,
SUPSITE.PRIMARY_PAY_SITE_FLAG,
TO_CHAR(SUPSITE.INACTIVE_DATE,'DD/MM/YYYY') AS INACTIVE_DT
FROM
POZ_SUPPLIERS SUP,
HZ_PARTIES HZP,
HZ_ADDTNL_PARTY_NAMES HZAP,
HZ_PARTY_SITES HZPS,
HZ_LOCATIONS HZL,
POZ_SUPPLIER_SITES_ALL_M SUPSITE,
FUN_ALL_BUSINESS_UNITS_V FABU
WHERE
SUP.PARTY_ID = HZP.PARTY_ID
AND HZP.PARTY_ID = HZAP.PARTY_ID(+)
and SUP.PARTY_ID = HZPS.PARTY_ID
AND HZAP.PARTY_NAME_TYPE(+) = 'PHONETIC'
AND HZAP.PREFERRED_FLAG(+) = 'Y'
and HZL.LOCATION_ID =HZPS.LOCATION_ID
AND SUP.VENDOR_ID = SUPSITE.VENDOR_ID
AND FABU.BU_ID = PRC_BU_ID
AND SUPSITE.PARTY_SITE_ID = HZPS.PARTY_SITE_ID
**********************
Unpaid AP INVOICES
**********************
SELECT DISTINCT
AIA.INVOICE_ID ACCOUNTSPAYABLEULID,
AIA.INVOICE_ID INVOICEULID,
AIA.INVOICE_NUM INVOICENUMBER,
TO_CHAR(AIA.INVOICE_DATE,'MM/DD/YYYY') INVOICEDATE,
AIA.INVOICE_AMOUNT INVOICETOTAL,
NVL ((SELECT SUM (APS.AMOUNT_REMAINING)
FROM AP_PAYMENT_SCHEDULES_ALL APS
WHERE APS.INVOICE_ID = AIA.INVOICE_ID),
0) INVOICEAMOUNTPAID,
TO_CHAR(AIA.INVOICE_DATE,'MM/DD/YYYY') INVOICERECEIVEDDATE,
TO_CHAR(AIA.CREATION_DATE,'MM/DD/YYYY') INVOICEENTRYDATE,
SUBSTR(AIA.SOURCE,1,20) INVOICESOURCE,
SUBSTR(AIA.INVOICE_TYPE_LOOKUP_CODE,1,10) INVOICETYPE,
SUBSTR(ABA.BATCH_NAME,1,50) INVOICEBATCHNUMBER,
NVL(AIA.DOC_SEQUENCE_VALUE, AIA.INVOICE_ID)VOUCHERNUMBER,
NVL ((SELECT SUM (APS.GROSS_AMOUNT)
FROM AP_PAYMENT_SCHEDULES_ALL APS
WHERE APS.INVOICE_ID = AIA.INVOICE_ID),
0) PAYMENTTOTAL,
(SELECT TO_CHAR(DUE_DATE,'MM/DD/YYYY')
FROM AP_PAYMENT_SCHEDULES_ALL APS
WHERE APS.INVOICE_ID = AIA.INVOICE_ID
AND ROWNUM = 1) PAYMENTDATE,
AIA.VENDOR_SITE_ID VENDORULID,
PS.SEGMENT1 VENDORNUMBER,
SUBSTR(HP.PARTY_NAME,1,50) VENDORNAME,
NVL(ROUND(AIA.EXCHANGE_RATE,6),1) EXCHANGERATETRXTORPT,
SUBSTR(AIA.INVOICE_CURRENCY_CODE,1,3) TRANSACTIONCURRENCYCOUNTRYCODE,
(SELECT SUBSTR(DESCRIPTION,1,20)
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'ATCAT EXTRACT'
AND LANGUAGE=USERENV('LANG')
AND ENABLED_FLAG='Y'
AND LOOKUP_CODE = HAUFT.NAME||'_COMPANYCODE')COMPANYCODE,
SUBSTR(HAUFT.NAME,1,20) BUSINESSUNIT,
SUBSTR(PSS.VENDOR_SITE_CODE,1,20) SITELOCATION,
(SELECT SUBSTR(DESCRIPTION,1,30)
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'ATCAT EXTRACT'
AND LANGUAGE=USERENV('LANG')
AND ENABLED_FLAG='Y'
AND LOOKUP_CODE = HAUFT.NAME||'_VOUCHERORIGIN') AS VOUCHERORIGIN,
SUBSTR(AP_TERMS.NAME,1,20) PAYMENTTERMS,
(SELECT SUBSTR(PAYMENT_METHOD_CODE,1,10) FROM AP_PAYMENT_SCHEDULES_ALL APS WHERE
APS.INVOICE_ID = AIA.INVOICE_ID
AND ROWNUM = 1)AS PAYMENTTYPE,
SUBSTR(AIA.LAST_UPDATED_BY,1,60) ENTEREDBY,
NULL AS "COMMENT",
(SELECT SUM(NVL(A1.UNROUNDED_TAX_AMT,0))FROM ZX_LINES A1 WHERE TRX_ID = AIA.INVOICE_ID) AS SALESTAXTOTAL,
NVL(ROUND(AIA.EXCHANGE_RATE,6),1) EXCHANGERATETRXTOACCTG,
SUBSTR(AIA.INVOICE_CURRENCY_CODE,1,3) ACCOUNTINGCURRENCYCOUNTRYCODE,
SUBSTR(AIA.ATTRIBUTE14,1,20) URN,
NULL LINKURL1,
NULL LINKTYPE1,
TO_CHAR(AIA.INVOICE_DATE,'YYYY') FISCALYEAR,
NULL POSTINGKEY,
NULL PAYMENTSTATUS,
NULL CONTRACTNUMBER,
NULL PURCHASEORDERINDICATOR
FROM AP_INVOICES_ALL AIA,
POZ_SUPPLIERS PS,
POZ_SUPPLIER_SITES_ALL_M PSS,
HZ_PARTIES HP,
AP_BATCHES_ALL ABA,
HR_ALL_ORGANIZATION_UNITS_F HAOUF,
HR_ORGANIZATION_UNITS_F_TL HAUFT,
AP_TERMS
WHERE 1=1
AND AIA.CANCELLED_DATE IS NULL
AND (NVL ( (SELECT SUM (APS.AMOUNT_REMAINING)
FROM AP_PAYMENT_SCHEDULES_ALL APS
WHERE APS.INVOICE_ID = AIA.INVOICE_ID),
0)<>0 AND AIA.PAYMENT_STATUS_FLAG <> 'Y')
AND NVL(AIA.INVOICE_AMOUNT,0)<>0
AND AIA.VENDOR_ID = PS.VENDOR_ID
AND PS.PARTY_ID = HP.PARTY_ID
AND ABA.BATCH_ID(+) = AIA.BATCH_ID
AND AIA.VENDOR_SITE_ID = PSS.VENDOR_SITE_ID
AND AP_TERMS.TERM_ID = AIA.TERMS_ID
AND HAOUF.ORGANIZATION_ID = HAUFT.ORGANIZATION_ID
AND HAUFT.ORGANIZATION_ID = AIA.ORG_ID
AND SYSDATE BETWEEN HAUFT.EFFECTIVE_START_DATE AND HAUFT.EFFECTIVE_END_DATE
AND HAUFT.EFFECTIVE_START_DATE = HAOUF.EFFECTIVE_START_DATE
AND HAUFT.EFFECTIVE_END_DATE = HAOUF.EFFECTIVE_END_DATE
AND HAUFT.LANGUAGE=USERENV('LANG')
AND HAUFT.NAME IN( :P_BUSINESS_UNIT)
**************
AP PAYMENTS
**************
SELECT DISTINCT
AIPA.INVOICE_PAYMENT_ID||'_'||AIPA.CHECK_ID||'_'||AIPA.INVOICE_ID PAYMENTHISTORYULID,
AIPA.INVOICE_ID APULID,
AIA.INVOICE_NUM INVOICENUMBER,
TO_CHAR(AIA.INVOICE_DATE,'MM/DD/YYYY') INVOICEDATE,
AIA.INVOICE_AMOUNT INVOICETOTAL,
NVL ((SELECT SUM (APS.AMOUNT_REMAINING)
FROM AP_PAYMENT_SCHEDULES_ALL APS
WHERE APS.INVOICE_ID = AIA.INVOICE_ID),
0) INVOICEAMOUNTPAID,
SUBSTR(AIA.INVOICE_TYPE_LOOKUP_CODE,1,10) INVOICETYPE,
AIPA.INVOICE_ID INVOICEULID,
ACA.CHECK_NUMBER PAYMENTNUMBER,
NVL ((SELECT SUM (APS.GROSS_AMOUNT)
FROM AP_PAYMENT_SCHEDULES_ALL APS
WHERE APS.INVOICE_ID = AIA.INVOICE_ID),
0) PAYMENTTOTAL,
TO_CHAR(ACA.CHECK_DATE,'MM/DD/YYYY') PAYMENTDATE,
ACA.CHECK_ID CHEQUEULID,
NVL(AIA.DOC_SEQUENCE_VALUE, ACA.CHECK_ID) VOUCHERNUMBER,
AIA.VENDOR_SITE_ID VENDORULID,
SUBSTR(PS.SEGMENT1,1,20) VENDORNUMBER,
NVL(SUBSTR(AIA.EXCHANGE_RATE,1,6),1) EXCHANGERATETRXTORPT,
SUBSTR(AIA.INVOICE_CURRENCY_CODE,1,3) TRANSACTIONCURRENCYCOUNTRYCODE,
(SELECT SUBSTR(DESCRIPTION,1,20)
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'ATCAT EXTRACT'
AND LANGUAGE=USERENV('LANG')
AND ENABLED_FLAG='Y'
AND LOOKUP_CODE = HAUFT.NAME||'_COMPANYCODE') COMPANYCODE,
NULL POSTINGKEY,
(SELECT SUBSTR(DESCRIPTION,1,30)
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'ATCAT EXTRACT'
AND LANGUAGE=USERENV('LANG')
AND ENABLED_FLAG='Y'
AND LOOKUP_CODE = HAUFT.NAME||'_VOUCHERORIGIN') VOUCHERORIGIN,
SUBSTR(AIA.SOURCE,1,20) INVOICESOURCE,
SUBSTR(ACA.PAYMENT_METHOD_CODE,1,10) PAYMENTMETHOD,-- DEFECT# NHP-128853
SUBSTR(AP_TERMS.NAME,1,20) PAYMENTTERMS,
NULL PURCHASEORDERINDICATOR,
TO_CHAR(APSA.DUE_DATE,'MM/DD/YYYY') PAYMENTDUEDATE,
TO_CHAR(APSA.DISCOUNT_DATE,'MM/DD/YYYY') PAYMENTDISCOUNTDUEDATE,
AIPA.DISCOUNT_TAKEN PAYMENTDISCOUNTAMOUNT,
TO_CHAR(AIA.INVOICE_DATE,'MM/DD/YYYY') INVOICERECEIVEDDATE,
SUBSTR(HP.PARTY_NAME,1,50) VENDORNAME,-- DEFECT# NHP-128853
SUBSTR(HAUFT.NAME,1,20) BUSINESSUNIT,
SUBSTR(AIA.CREATED_BY,1,60) ENTEREDBY,
NULL LATEPAYMENTFEES,
TO_CHAR(AIA.CREATION_DATE,'MM/DD/YYYY') INVOICEENTRYDATE,
TO_CHAR(AIPA.ACCOUNTING_DATE,'MM/DD/YYYY') POSTDATE,
TO_CHAR(ACA.CLEARED_DATE,'MM/DD/YYYY') CLEARDATE,
CASE WHEN (ACA.STATUS_LOOKUP_CODE = 'NEGOTIABLE' OR ACA.STATUS_LOOKUP_CODE='CLEARED')
THEN 'P'
ELSE 'V'
END AS PAYMENTSTATUS,
AIPA.DISCOUNT_TAKEN CASHDISCOUNTAMOUNT,
ROUND(NVL(AIPA.EXCHANGE_RATE,1),6) EXCHANGERATETRXTOACCTG,
SUBSTR(AIA.INVOICE_CURRENCY_CODE,1,3) ACCOUNTINGCURRENCYCOUNTRYCODE,
SUBSTR(AIA.ATTRIBUTE14,1,20) URN,
NULL LINKURL1,
NULL LINKTYPE1,
TO_CHAR(ACA.CHECK_DATE,'YYYY') FISCALYEAR,
NULL INVOICEAPPROVALDATE,
NULL CONTRACTNUMBER,
NULL AS "COMMENT"
FROM
AP_INVOICE_PAYMENTS_ALL AIPA,
AP_PAYMENT_SCHEDULES_ALL APSA,
AP_CHECKS_ALL ACA,
AP_INVOICES_ALL AIA,
POZ_SUPPLIERS PS,
POZ_SUPPLIER_SITES_ALL_M PSS,
HZ_PARTIES HP,
--AP_BATCHES_ALL ABA,
HR_ALL_ORGANIZATION_UNITS_F HAOUF,
HR_ORGANIZATION_UNITS_F_TL HAUFT,
AP_TERMS
WHERE 1=1
AND ACA.STATUS_LOOKUP_CODE <> 'VOIDED' -- DEFECT# NHP-128853
AND AIPA.INVOICE_ID = AIA.INVOICE_ID
AND AIPA.CHECK_ID(+) = ACA.CHECK_ID
AND AIA.VENDOR_ID = PS.VENDOR_ID
AND PS.PARTY_ID = HP.PARTY_ID
AND APSA.INVOICE_ID = AIPA.INVOICE_ID
AND APSA.PAYMENT_NUM = AIPA.PAYMENT_NUM
--AND ABA.BATCH_ID(+) = AIA.BATCH_ID
AND AIA.VENDOR_SITE_ID = PSS.VENDOR_SITE_ID
AND AP_TERMS.TERM_ID = AIA.TERMS_ID
AND HAOUF.ORGANIZATION_ID = HAUFT.ORGANIZATION_ID
AND HAUFT.ORGANIZATION_ID = AIA.ORG_ID
AND SYSDATE BETWEEN HAUFT.EFFECTIVE_START_DATE AND HAUFT.EFFECTIVE_END_DATE
AND HAUFT.EFFECTIVE_START_DATE = HAOUF.EFFECTIVE_START_DATE
AND HAUFT.EFFECTIVE_END_DATE = HAOUF.EFFECTIVE_END_DATE
AND HAUFT.LANGUAGE=USERENV('LANG')
AND HAUFT.NAME IN (:P_BUSINESS_UNIT)
AND DECODE (:P_FULL_LOAD,'N', TRUNC(AIPA.LAST_UPDATE_DATE),
SYSDATE) >= DECODE(:P_FULL_LOAD,'N',
TO_DATE(:P_LAST_RUN_DATE,'DD-MM-YYYY'), SYSDATE)
select * from AP_INVOICES_INTERFACE where to_char(creation_date ,'dd-mm-yyyy') = '26-03-2025'
select * from AP_INTERFACE_REJECTIONS where to_char(creation_date ,'dd-mm-yyyy') = '26-03-2025'
select * from AP_INVOICE_LINES_INTERFACE where to_char(creation_date ,'dd-mm-yyyy') = '26-03-2025'
select * from po_headers_all where SEGMENT1 in ('P100745US012','P117460US001','P100755US012','P117509US001','P117568US001','Not ound','P100765US012','P117511US001','P117583US001','P117487US001','P117489US001','P117512US001','P117506US001','P117522US001','P117516US001','P100754US012','P106672US024','P117535US001','P117484US001','P10052US027','P117508US001','P117464US001','P117517US001','P117456US001','P106692US024','P117492US001','P117491US001','P117502US001','P117459US001','P117496US001','P106673US024','P117486US001','P117520US001','P117493US001','P117545US001','P117507US001','P117461US001','P117572US001','P100764US012','P100756US012','P117494US001','P117519US001','P117515US001','P117531US001','P117485US001','P117523US001','P117514US001','P117458US001')
**********************
Location Information
**********************
SELECT
LOCATION_CODE,
LOCATION_NAME,
DESCRIPTION,
ACTIVE_STATUS,
SHIP_TO_SITE_FLAG,
RECEIVING_SITE_FLAG,
BILL_TO_SITE_FLAG,
TELEPHONE_NUMBER_1,
ADDRESS_LINE_1,
ADDRESS_LINE_2,
ADDRESS_LINE_3,
ADDRESS_LINE_4,
BUILDING,
FLOOR_NUMBER,
COUNTRY,
POSTAL_CODE,
TOWN_OR_CITY,
EMAIL_ADDRESS
FROM HR_LOCATIONS_ALL_F_VL
********************
Lookup Code Values
********************
select FLV.LOOKUP_TYPE,
FLV.LOOKUP_CODE,
FLV.MEANING,
FLV.DESCRIPTION,
FLV.ENABLED_FLAG,
FLV.START_DATE_ACTIVE,
FLV.END_DATE_ACTIVE,
FLV.SET_ID,
FLT.MEANING LOOKUP_MEANING,
FLT.DESCRIPTION LOOKUP_DESCRIPTION
from
FND_LOOKUP_VALUES FLV,
FND_LOOKUP_TYPES_TL FLT
where
FLV.LANGUAGE = 'US'
and FLV.LOOKUP_TYPE = 'EXM_VEHICLE_TYPE_IPOV'
and FLV.LOOKUP_TYPE = FLT.LOOKUP_TYPE
and FLT.LANGUAGE = 'US'
******************
Related Valueset
*****************
select
FFVV.FLEX_VALUE as LE,
FFVV1.FLEX_VALUE as Account_Val,
FVRV.Enabled_Flag
from
FND_VS_RELATED_VALUES FVRV,
fnd_id_flex_segments FIFS,
FND_FLEX_VALUES_VL FFVV,
fnd_id_flex_segments FIFS1,
FND_FLEX_VALUES_VL FFVV1
where FVRV.value_set_code1 = 'Account WSP'
and FVRV.value_set_code2 = 'Legal Entity WSP'
and FIFS.SEGMENT_NAME = 'Legal Entity WSP'
and FIFS.FLEX_VALUE_SET_ID = FFVV.FLEX_VALUE_SET_ID
and FIFS.application_id = '101'
and FIFS1.SEGMENT_NAME = 'Account WSP'
and FIFS1.FLEX_VALUE_SET_ID = FFVV1.FLEX_VALUE_SET_ID
and FIFS1.application_id = '101'
and FVRV.value_id2 = ffvv.flex_value_idValueS
and FVRV.value_id1 = ffvv1.flex_value_id
and FFVV.FLEX_VALUE in ('350009','350006','340000','330025', '330018','350010','350020','330023','330003','330000')
and FFVV1.FLEX_VALUE in ('54000802')