<?xml version = '1.0' encoding = 'UTF-8'?>
<dataTemplate name="XXUNVAPDETAUDRPT" defaultPackage="XXUNV_AP_DET_RPT_BURST_PKG" description="XXUNV AP Details Audit Report" version="1.0">
<properties><property name="db_fetch_size" value="400" /></properties>
<parameters>
<parameter name="P_FROM_DATE" dataType="character"/>
<parameter name="P_TO_DATE" dataType="character"/>
<parameter name="P_COMP_CODE" dataType="character"/>
<parameter name="P_DEP_CODE" dataType="character"/>
<parameter name="P_INV_MTL_CAT1" dataType="character"/>
<parameter name="P_INV_MTL_CAT2" dataType="character"/>
<parameter name="P_PO_BUYER" dataType="number"/>
<parameter name="P_VENDOR_STATE" dataType="character"/>
<parameter name="P_DELIVER_STATE" dataType="character"/>
<parameter name="P_PO_NUM" dataType="character"/>
<parameter name="P_VENDOR_NUM" dataType="character"/>
<parameter name="P_EMAIL" dataType="character"/>
</parameters>
<dataQuery>
<sqlStatement name="Q1">
<![CDATA[
SELECT
ap.check_date check_date,
ap.payment_method_code,
ap.payment_amount,
ap.bank_account_name,
ap.check_id,
ap.check_number,
ap.status_lookup_code,
ap.creation_date creation_date,
ap.currency_code,
ap.vendor_name,
ap.vendor_id,
ap.segment1,
ap.vendor_site_code,
ap.address_line1,
ap.address_line2,
ap.address_line3,
ap.city,
ap.state,
ap.zip,
ap.invoice_amount,
ap.invoice_date invoice_date,
ap.invoice_id,
ap.invoice_num,
ap.last_updated_by,
ap.updated_by,
ap.payment_terms,
ap.sob_desc,
po.po_number,
po.buyer,
po.category,
po.category2,
po.location_code,
po.address_line_1,
po.town_or_city,
po.postal_code,
po.state state1,
ap.invoice_distribution_id,
ap.amount,
ap.accounting_date accounting_date,
ap.account_code,
ap.company,
ap.Company_Name,
ap.account,
ap.account_name,
ap.department department,
ap.department_name,
ap.program program,
ap.program_name,
ap.responsibility responsibility,
ap.responsibility_name,
ap.intercompany intercompany,
ap.tbd tbd,
ap.cleared_date cleared_date,
ap.project_number,
ap.task_number,
ap.image_url,
TO_CHAR(SYSDATE,'DDMONYYHHMMSS') sysdate1
FROM
(
SELECT
ac.check_date check_date,
ac.amount Payment_Amount,
ac.bank_account_name,
ac.check_id,
ac.check_number,
ac.creation_date creation_date,
ac.currency_code,
ac.status_lookup_code,
api.invoice_amount,
api.invoice_date invoice_date,
api.invoice_id,
api.invoice_num,
api.last_updated_by,
f.description Updated_By,
(SELECT name
FROM ap.ap_terms_tl apt
WHERE apt.language = 'US'
AND apt.term_id = api.terms_id) Payment_terms,
gll.name SOB_Desc,
api.payment_method_code,
aps.vendor_name,
aps.vendor_id,
aps.segment1,
apss.vendor_site_code,
apss.address_line1,
apss.address_line2,
apss.address_line3,
apss.city,
apss.state,
apss.zip,
apd.invoice_distribution_id,
apd.amount,
apd.accounting_date accounting_date,
(gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||gcc.segment4||'-'||gcc.segment5||'-'||gcc.segment6||'-'||gcc.segment7) Account_Code,
gcc.segment1 Company,
(SELECT dvl.description
FROM apps.fnd_flex_value_sets dv,
apps.fnd_flex_values_vl dvl
WHERE dv.flex_value_set_name = 'UVN_GL_COMPANY'
AND dv.flex_value_set_id = dvl.flex_value_set_id
AND dvl.flex_value = gcc.segment1) company_name,
gcc.segment2 Account,
(SELECT dvl.description
FROM apps.fnd_flex_value_sets dv,
apps.fnd_flex_values_vl dvl
WHERE dv.flex_value_set_name = 'UVN_GL_NATURAL_ACCOUNT'
AND dv.flex_value_set_id = dvl.flex_value_set_id
AND dvl.flex_value = gcc.segment2) account_name,
gcc.segment3 Department,
(SELECT dvl.description
FROM apps.fnd_flex_value_sets dv,
apps.fnd_flex_values_vl dvl
WHERE dv.flex_value_set_name = 'UVN_GL_DEPARTMENT'
AND dv.flex_value_set_id = dvl.flex_value_set_id
AND dvl.flex_value = gcc.segment3) department_name,
gcc.segment4 Program,
(SELECT dvl.description
FROM apps.fnd_flex_value_sets dv,
apps.fnd_flex_values_vl dvl
WHERE dv.flex_value_set_name = 'UVN_GL_PROGRAM_EVENTS'
AND dv.flex_value_set_id = dvl.flex_value_set_id
AND dvl.flex_value = gcc.segment4) program_name,
gcc.segment5 Responsibility,
(SELECT dvl.description
FROM apps.fnd_flex_value_sets dv,
apps.fnd_flex_values_vl dvl
WHERE dv.flex_value_set_name = 'UVN_GL_RESP_CENTER'
AND dv.flex_value_set_id = dvl.flex_value_set_id
AND dvl.flex_value = gcc.segment5) responsibility_name,
gcc.segment6 Intercompany,
gcc.segment7 TBD,
apd.po_distribution_id,
ac.cleared_date cleared_date,
api.attribute7 image_url,
ppt.project_number,
ppt.task_number
FROM
ap.ap_checks_all ac
INNER JOIN ap.ap_invoice_payments_all aip
ON( ac.check_id = aip.check_id
AND ac.status_lookup_code <> 'VOIDED'
AND ac.check_date >= TO_DATE(:P_FROM_DATE,'YYYY/MM/DD HH24:MI:SS')
AND ac.check_date <= TO_DATE(:P_TO_DATE,'YYYY/MM/DD HH24:MI:SS') )
INNER JOIN ap.ap_invoices_all api
ON(api.invoice_id = aip.invoice_id
--AND api.set_of_Books_id = aip.set_of_Books_id
AND api.org_id = aip.org_id
--AND Api.Set_Of_Books_Id = '1001'
AND api.org_id = FND_PROFILE.VALUE('ORG_ID')
)
INNER JOIN ap.ap_invoice_distributions_all apd
ON( api.invoice_id = apd.invoice_id
AND apd.set_of_Books_id = api.set_of_Books_id)
INNER JOIN gl.gl_code_combinations gcc
ON(apd.dist_code_combination_id = gcc.code_combination_id)
INNER JOIN ap.ap_suppliers aps
ON(aps.vendor_id = api.vendor_id)
INNER JOIN ap.ap_supplier_sites_all apss
ON( apss.vendor_id = aps.vendor_id
AND apss.vendor_site_id = api.vendor_site_id)
INNER JOIN gl.gl_ledgers gll
ON( Gll.Ledger_Id = Api.Set_Of_Books_Id)
INNER JOIN fnd_user f
ON( f.user_id = api.last_updated_by )
LEFT JOIN (SELECT pp.project_id,
pp.segment1 project_number,
pt.task_id,
pt.task_number
FROM pa.pa_projects_all pp,
pa.pa_tasks pt
WHERE pp.project_id = pt.project_id) ppt
ON (apd.project_id = ppt.project_id
AND apd.task_id = ppt.task_id)
WHERE 1 = 1
AND NVL(gcc.segment3,'X') = NVL(:P_DEP_CODE,NVL(gcc.segment3,'X'))
AND NVL(apss.state, 'X')= NVL(:P_VENDOR_STATE, NVL(apss.state,'X'))
AND aps.segment1 = NVL(:P_VENDOR_NUM,aps.segment1)
) ap
LEFT OUTER JOIN (
SELECT
poh.segment1 po_number,
f2.description buyer,
cat.segment1 category,
cat.segment2 category2,
hrl.location_code,
hrl.address_line_1,
hrl.address_line_2,
hrl.address_line_3,
hrl.town_or_city,
hrl.postal_code,
hrl.region_2 state,
pod.po_distribution_id po_dist,
f2.user_id user_id
FROM po.po_headers_all poh
INNER JOIN po.po_lines_all pll
ON(poh.po_header_id = pll.po_header_id)
INNER JOIN po.po_distributions_all pod
ON(pod.po_line_id = pll.po_line_id)
INNER JOIN fnd_user f2
ON( f2.user_id = poh.created_by)
INNER JOIN inv.mtl_categories_b cat
ON(cat.category_id = pll.category_id)
INNER JOIN hr.hr_locations_all hrl
ON( hrl.location_id = pod.deliver_to_location_id )
WHERE 1=1
) po ON ap.po_distribution_id = po.po_dist
WHERE 1=1
AND NVL(po.user_id,1) = NVL(:P_PO_BUYER, NVL(po.user_id,1))
AND NVL(po.state, 'X')= NVL(:P_DELIVER_STATE, NVL(po.state,'X'))
AND NVL(po.po_number,1) = NVL(:P_PO_NUM, NVL(po.po_number,1))
AND NVL(UPPER(po.category), 'X') = NVL(UPPER(:P_INV_MTL_CAT1), NVL(UPPER(po.category),'X'))
AND NVL(UPPER(po.category2),'X') = NVL(UPPER(:P_INV_MTL_CAT2),NVL(UPPER(po.category2),'X'))
]]>
</sqlStatement>
</dataQuery>
<dataTrigger name="beforeReport" source="XXUNV_AP_DET_RPT_BURST_PKG.beforeReport"/>
<dataStructure>
<group name="G_AP_DTLS" source="Q1">
<element name="CHECK_DATE" value="CHECK_DATE"/>
<element name="PAYMENT_METHOD_CODE" value="PAYMENT_METHOD_CODE"/>
<element name="PAYMENT_AMOUNT" value="PAYMENT_AMOUNT"/>
<element name="BANK_ACCOUNT_NAME" value="BANK_ACCOUNT_NAME"/>
<element name="CHECK_ID" value="CHECK_ID"/>
<element name="CHECK_NUMBER" value="CHECK_NUMBER"/>
<element name="STATUS_LOOKUP_CODE" value="STATUS_LOOKUP_CODE"/>
<element name="CREATION_DATE" value="CREATION_DATE"/>
<element name="CURRENCY_CODE" value="CURRENCY_CODE"/>
<element name="VENDOR_NAME" value="VENDOR_NAME"/>
<element name="VENDOR_ID" value="VENDOR_ID"/>
<element name="SEGMENT1" value="SEGMENT1"/>
<element name="VENDOR_SITE_CODE" value="VENDOR_SITE_CODE"/>
<element name="ADDRESS_LINE1" value="ADDRESS_LINE1"/>
<element name="ADDRESS_LINE2" value="ADDRESS_LINE2"/>
<element name="ADDRESS_LINE3" value="ADDRESS_LINE3"/>
<element name="CITY" value="CITY"/>
<element name="STATE" value="STATE"/>
<element name="ZIP" value="ZIP"/>
<element name="INVOICE_AMOUNT" value="INVOICE_AMOUNT"/>
<element name="INVOICE_DATE" value="INVOICE_DATE"/>
<element name="INVOICE_ID" value="INVOICE_ID"/>
<element name="INVOICE_NUM" value="INVOICE_NUM"/>
<element name="LAST_UPDATED_BY" value="LAST_UPDATED_BY"/>
<element name="UPDATED_BY" value="UPDATED_BY"/>
<element name="PAYMENT_TERMS" value="PAYMENT_TERMS"/>
<element name="SOB_DESC" value="SOB_DESC"/>
<element name="PO_NUMBER" value="PO_NUMBER"/>
<element name="BUYER" value="BUYER"/>
<element name="CATEGORY" value="CATEGORY"/>
<element name="CATEGORY2" value="CATEGORY2"/>
<element name="LOCATION_CODE" value="LOCATION_CODE"/>
<element name="ADDRESS_LINE_1" value="ADDRESS_LINE_1"/>
<element name="TOWN_OR_CITY" value="TOWN_OR_CITY"/>
<element name="POSTAL_CODE" value="POSTAL_CODE"/>
<element name="STATE1" value="STATE1"/>
<element name="INVOICE_DISTRIBUTION_ID" value="INVOICE_DISTRIBUTION_ID"/>
<element name="AMOUNT" value="AMOUNT"/>
<element name="ACCOUNTING_DATE" value="ACCOUNTING_DATE"/>
<element name="ACCOUNT_CODE" value="ACCOUNT_CODE"/>
<element name="COMPANY" value="COMPANY"/>
<element name="COMPANY_NAME" value="COMPANY_NAME"/>
<element name="ACCOUNT" value="ACCOUNT"/>
<element name="ACCOUNT_NAME" value="ACCOUNT_NAME"/>
<element name="DEPARTMENT" value="DEPARTMENT"/>
<element name="DEPARTMENT_NAME" value="DEPARTMENT_NAME"/>
<element name="PROGRAM" value="PROGRAM"/>
<element name="PROGRAM_NAME" value="PROGRAM_NAME"/>
<element name="RESPONSIBILITY" value="RESPONSIBILITY"/>
<element name="RESPONSIBILITY_NAME" value="RESPONSIBILITY_NAME"/>
<element name="INTERCOMPANY" value="INTERCOMPANY"/>
<element name="TBD" value="TBD"/>
<element name="CLEARED_DATE" value="CLEARED_DATE"/>
<element name="PROJECT_NUMBER" value="PROJECT_NUMBER"/>
<element name="TASK_NUMBER" value="TASK_NUMBER"/>
<element name="IMAGE_URL" value="IMAGE_URL"/>
<element name="SYSDATE1" value="SYSDATE1"/>
</group>
</dataStructure>
<dataTrigger name="afterReport" source="XXUNV_AP_DET_RPT_BURST_PKG.afterReport"/>
</dataTemplate>
No comments:
Post a Comment