SELECT
dha.source_order_id AS sourcetransactionidentifier,
dha.order_number AS sourcetransactionnumber,
dha.source_order_system AS sourcetransactionsystem,
dha.transactional_currency_code AS transactionalcurrencycode,
TO_CHAR(dha.ordered_date,’YYYY/MM/DD HH24:MI:SS’) AS transactionon,
dha.order_type_code AS transactiontypecode,
dha.legal_entity_id AS requestinglegalentity,
dha.partial_ship_allowed_flag AS partialshipallowedflag,
dha.freeze_price_flag AS freezepricing,
dha.freeze_shipping_charge_flag AS freezeshippingcharge,
dha.freeze_tax_flag AS freezetax,
dla.line_id AS order_line_id,
dla.source_line_id AS sourcetransactionlineidentifier,
dla.line_number AS orderline,
dla.source_schedule_id AS sourcetransactionscheduleidentifier,
dla.source_schedule_number AS sourcetransactionschedulenumber,
dla.source_line_number AS sourcetransactionlinenumber,
dla.ordered_qty AS orderedquantity,
(
SELECT
iumv.unit_of_measure
FROM
inv_units_of_measure_vl iumv
WHERE
1 = 1
AND iumv.uom_code = dla.ordered_uom
) AS ordereduom,
dla.category_code AS transactioncategorycode,
TO_CHAR(dfla.request_ship_date,’YYYY/MM/DD HH24:MI:SS’) AS requestedshipdate,
dla.schedule_ship_date AS scheduleshipdate,
dla.unit_list_price AS unitlistprice,
dla.unit_selling_price AS unitsellingprice,
dfla.orig_sys_document_ref AS origsysdocumentreference,
dfla.orig_sys_document_line_ref AS origsysdocumentlinereference,
dfla.status_code AS solinestatus,
dfleb1.attribute_char2 AS project_id,
dfleb.attribute_char1 AS activity_id,
haou.name AS requestingbusinessunit,
doa.party_id AS partyid,
haou.organization_id AS org_id,
iso.sold_to_party_id AS buyingpartyidentifier,
hp.party_name AS buyingpartyname,
dla.inventory_item_id AS inventory_item_id,
dla.inventory_organization_id AS inventory_organization_id,
esi.item_number AS productnumber,
esi.inventory_item_id AS product_id,
hp.party_number AS partynumber,
acct.cust_account_id AS customer_id,
acct.account_number AS cust_account_number,
party_site.party_site_id AS party_site_id,
(
SELECT
hcsu.site_use_id
FROM
hz_cust_site_uses_all hcsu
WHERE
1 = 1
AND hcas.cust_acct_site_id = hcsu.cust_acct_site_id
AND hcsu.site_use_code = ‘BILL_TO’
) AS accountsiteidentifier,
dha.source_order_number AS source_order_number,
(
SELECT
ppab.segment1
FROM
pjf_projects_all_b ppab,
doo_projects dp
WHERE
1 = 1
AND dp.pjc_project_id = ppab.project_id
AND dp.parent_entity_id = dfla.fulfill_line_id
) AS project_number,
(
SELECT
pjf.task_number
FROM
pjf_tasks_v pjf,
pjf_projects_all_b ppab,
doo_projects dp
WHERE
1 = 1
AND dp.pjc_project_id = ppab.project_id
AND dp.parent_entity_id = dfla.fulfill_line_id
AND dp.pjc_task_id = pjf.task_id
AND ppab.project_id = pjf.project_id
) AS task_number,
(
SELECT
pet.expenditure_type_name
FROM
doo_projects dp,
pjf_exp_types_tl pet
WHERE
1 = 1
AND dp.parent_entity_id = nvl(dfla.reference_fline_id,dfla.fulfill_line_id)
AND dp.pjc_expenditure_type_id = pet.expenditure_type_id
) AS expenditure_type_name,
(
SELECT
hao.name
FROM
doo_projects dp,
hr_all_organization_units_x hao
WHERE
1 = 1
AND dp.parent_entity_id = dfla.fulfill_line_id
AND dp.pjc_organization_id = hao.organization_id
) AS expenditure_organisation,
TO_CHAR( (
SELECT
pjc_expenditure_item_date
FROM
doo_projects prj
WHERE
1 = 1
AND prj.parent_entity_id = nvl(dfla.reference_fline_id,dfla.fulfill_line_id)
),’YYYY/MM/DD’) AS expenditure_item_date,
(
SELECT
esit.description
FROM
egp_system_items_tl esit
WHERE
1 = 1
AND esit.inventory_item_id = esi.inventory_item_id
AND esit.organization_id = esi.organization_id
) AS item_description,
dha.change_version_number AS revision,
dha.status_code AS header_status
FROM
doo_headers_all dha,
doo_lines_all dla,
doo_fulfill_lines_all dfla,
doo_fulfill_lines_eff_b dfleb,
hr_operating_units hou,
doo_order_addresses doa,
hr_all_organization_units haou,
doo_fulfill_lines_eff_b dfleb1,
inv_sales_orders iso,
hz_parties hp,
egp_system_items_b esi,
inv_organization_definitions_v iodv,
hz_cust_accounts acct,
hz_party_sites party_site,
hz_cust_acct_sites_all hcas,
fun_names_business_units_v fnbuv,
xle_entity_profiles xlep
WHERE
1 = 1
AND dha.header_id = dla.header_id
AND dha.header_id = dfla.header_id
AND dla.header_id = dfla.header_id
AND dla.line_id = dfla.line_id
AND dfla.fulfill_line_id = dfleb.fulfill_line_id (+)
AND dha.submitted_flag = ‘Y’
AND dfla.status_code IN (
‘BACKORDERED’,
‘AWAIT_SHIP’
)
AND dfleb.context_code (+) = ‘P6 Reference’
AND dfleb.attribute_char1 IS NOT NULL
AND dha.org_id = hou.organization_id
AND dha.header_id = doa.header_id (+)
AND hou.organization_id = haou.organization_id
AND dfleb1.context_code (+) = ‘P6 Reference’
AND dfla.fulfill_line_id = dfleb1.fulfill_line_id (+)
AND dha.sold_to_party_id = hp.party_id
AND hp.party_id = iso.sold_to_party_id
AND esi.inventory_item_id = dla.inventory_item_id (+)
AND esi.organization_id = iodv.organization_id
–AND iodv.ORGANIZATION_CODE = ‘VSY’
AND doa.cust_acct_id = acct.cust_account_id (+)
AND party_site.party_site_id (+) = doa.party_site_id
AND party_site.party_id = hp.party_id
AND party_site.party_site_id = hcas.party_site_id
AND dha.org_id = fnbuv.bu_id
AND hou.organization_id = fnbuv.bu_id
AND hou.default_legal_context_id = xlep.legal_entity_id
AND fnbuv.bu_name = nvl(:p_bu_name,fnbuv.bu_name)
AND dha.order_number = nvl(:p_order_num,dha.order_number)
AND dha.status_code = nvl(:p_status_code,dha.status_code)
AND xlep.name = nvl(:p_le_name,xlep.name)
ORDER BY
dha.order_number,
dla.line_id
Recent Posts