[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DPP_EXECUTION_DETAIL_ID_SEQ.nextval
INTO l_exe_dtl_id
FROM dual;
SELECT user_name
INTO l_user_name
FROM fnd_user
WHERE user_id = l_user_id ;
DELETE FROM DPP_TRANSACTION_LINES_GT;
INSERT INTO DPP_TRANSACTION_LINES_GT(transaction_header_id,
transaction_line_id
)
VALUES(p_txn_hdr_rec.Transaction_Header_ID,
p_txn_line_id(i)
);
INSERT INTO DPP_TRANSACTION_LINES_GT(transaction_header_id,
transaction_line_id)
SELECT transaction_header_id,
transaction_line_id
FROM dpp_transaction_lines_all
WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID;
INSERT INTO DPP_TRANSACTION_LINES_GT(transaction_header_id,
transaction_line_id)
SELECT transaction_header_id,
customer_inv_line_id
FROM dpp_customer_claims_all
WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID;
l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
transaction_number,
org_id,
Vendor_id,'
||l_user_name|| ' user_name, '
||l_user_id||'user_id,'
||l_exe_dtl_id||'Execution_detail_id,
CURSOR(select transaction_line_id,
inventory_item_id,
supplier_new_price new_price,
UOM,
headers.trx_currency currency
FROM dpp_transaction_lines_all lines
WHERE headers.Transaction_header_id = lines.Transaction_header_id
AND nvl(lines.UPDATE_ITEM_LIST_PRICE,''N'') = ''N'') LINES
FROM dpp_transaction_headers_all headers
WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
'AND EXISTS (SELECT Transaction_header_id
FROM dpp_transaction_lines_all
WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
AND nvl(UPDATE_ITEM_LIST_PRICE,''N'') = ''N'')'
);
UPDATE DPP_TRANSACTION_LINES_ALL
SET update_item_list_price = 'P',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id
WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
AND nvl(update_item_list_price, 'N') = 'N';
DPP_UTILITY_PVT.debug_message('Unable to Update the column update_item_list_price in DPP_TRANSACTION_LINES_ALL Table');
l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
Transaction_number,
to_char(Effective_start_date,''YYYY-MM-DD'') Effectivity_date,
org_id,
Vendor_id,
Vendor_site_id,'
||l_user_name|| ' user_name, '
||l_user_id||'user_id,'
||l_exe_dtl_id||'Execution_detail_id,
CURSOR(select transaction_line_id,
inventory_item_id,
supplier_new_price new_price,
headers.trx_currency currency
FROM dpp_transaction_lines_all lines
WHERE headers.Transaction_header_id = lines.Transaction_header_id
AND nvl(lines.NOTIFY_OUTBOUND_PRICELIST,''N'') <> ''D'') LINES
FROM dpp_transaction_headers_all headers
WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
'AND EXISTS (SELECT Transaction_header_id
FROM dpp_transaction_lines_all
WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
AND nvl(NOTIFY_OUTBOUND_PRICELIST,''N'') <> ''D'')'
);
UPDATE DPP_TRANSACTION_LINES_ALL
SET NOTIFY_OUTBOUND_PRICELIST = 'P',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id
WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
AND nvl(NOTIFY_OUTBOUND_PRICELIST,'N') <> 'D';
DPP_UTILITY_PVT.debug_message('Unable to Update the column NOTIFY_OUTBOUND_PRICELIST in DPP_TRANSACTION_LINES_ALL Table');
SELECT SUM(nvl(claim_amount,0))
INTO l_claim_hdr_amt
FROM dpp_transaction_lines_all
WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
AND to_number(supp_dist_claim_id) = p_txn_hdr_rec.claim_id;
l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
Transaction_number,
Vendor_id,
org_id,
Vendor_site_id,'
||l_user_name|| ' user_name, '
||l_user_id||'user_id,'
||l_exe_dtl_id||'Execution_detail_id,'
||l_status_code||'Status_code,
trx_currency,'
||l_claim_hdr_amt||'Claim_amount,'
||p_txn_hdr_rec.claim_id||'claim_id,
CURSOR(select transaction_line_id,
inventory_item_id,
claim_amount claim_amount,
approved_inventory claim_quantity,
UOM,'
||p_txn_hdr_rec.claim_id||'claim_id,
headers.trx_currency currency
FROM dpp_transaction_lines_all lines
WHERE headers.Transaction_header_id = lines.Transaction_header_id
AND lines.supp_dist_claim_id = '||p_txn_hdr_rec.claim_id||') LINES
FROM dpp_transaction_headers_all headers
WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID
);
l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
Transaction_number,
to_char(Effective_start_date,''YYYY-MM-DD'') Effectivity_date,
org_id,
Vendor_id,
Vendor_site_id,'
||l_user_name|| ' user_name, '
||l_user_id||'user_id,'
||l_exe_dtl_id||'Execution_detail_id,
CURSOR(select transaction_line_id,
inventory_item_id,
supplier_new_price new_price,
headers.trx_currency currency
FROM dpp_transaction_lines_all lines
WHERE headers.Transaction_header_id = lines.Transaction_header_id
AND nvl(lines.NOTIFY_INBOUND_PRICELIST,''N'') <> ''D'') LINES
FROM dpp_transaction_headers_all headers
WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
'AND EXISTS (SELECT Transaction_header_id
FROM dpp_transaction_lines_all
WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
AND nvl(NOTIFY_INBOUND_PRICELIST,''N'') <> ''D'')'
);
UPDATE DPP_TRANSACTION_LINES_ALL
SET NOTIFY_INBOUND_PRICELIST = 'P',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id
WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
AND nvl(NOTIFY_INBOUND_PRICELIST,'N') <> 'D';
DPP_UTILITY_PVT.debug_message('Unable to Update the column NOTIFY_INBOUND_PRICELIST in DPP_TRANSACTION_LINES_ALL Table');
l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
Transaction_number,
to_char(Effective_start_date,''YYYY-MM-DD'') Effectivity_date,
org_id,
Vendor_id,
Vendor_site_id,'
||l_user_name|| ' user_name, '
||l_user_id||'user_id,'
||l_exe_dtl_id||'Execution_detail_id,
CURSOR(select transaction_line_id,
inventory_item_id,
supplier_new_price new_price,
headers.trx_currency currency
FROM dpp_transaction_lines_all lines
WHERE headers.Transaction_header_id = lines.Transaction_header_id
AND nvl(lines.NOTIFY_PROMOTIONS_PRICELIST,''N'') <> ''D'') LINES
FROM dpp_transaction_headers_all headers
WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
'AND EXISTS (SELECT Transaction_header_id
FROM dpp_transaction_lines_all
WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
AND nvl(NOTIFY_PROMOTIONS_PRICELIST,''N'') <> ''D'')'
);
UPDATE DPP_TRANSACTION_LINES_ALL
SET NOTIFY_PROMOTIONS_PRICELIST = 'P',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id
WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
AND nvl(NOTIFY_PROMOTIONS_PRICELIST,'N') <> 'D';
DPP_UTILITY_PVT.debug_message('Unable to Update the column NOTIFY_PROMOTIONS_PRICELIST in DPP_TRANSACTION_LINES_ALL Table');
SELECT ostp.gl_cost_adjustment_acct
INTO l_cost_adj_acct
FROM ozf_supp_trd_prfls_all ostp,
dpp_transaction_headers_all dtha
WHERE ostp.supplier_id = to_number(dtha.vendor_id)
AND ostp.supplier_site_id = to_number(dtha.vendor_site_id)
AND ostp.org_id = to_number(dtha.org_id)
AND dtha.transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID;
SELECT osp.gl_cost_adjustment_acct
INTO l_cost_adj_acct
FROM ozf_sys_parameters osp,
dpp_transaction_headers_all dtha
WHERE osp.org_id = to_number(dtha.org_id)
AND dtha.transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID;
l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
Transaction_number,
org_id,'
||l_user_name|| ' user_name, '
||l_user_id||'user_id,'
||l_exe_dtl_id||'Execution_detail_id,'
||l_cost_adj_acct||'gl_cost_adjustment_acct,
CURSOR(select transaction_line_id,
inventory_item_id,
supplier_new_price new_price,
headers.trx_currency currency,
UOM,
price_change
FROM dpp_transaction_lines_all lines
WHERE headers.Transaction_header_id = lines.Transaction_header_id
AND nvl(lines.UPDATE_INVENTORY_COSTING,''N'') = ''N'') LINES
FROM dpp_transaction_headers_all headers
WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
'AND EXISTS (SELECT Transaction_header_id
FROM dpp_transaction_lines_all
WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
AND nvl(UPDATE_INVENTORY_COSTING,''N'') = ''N'')'
);
UPDATE DPP_TRANSACTION_LINES_ALL
SET UPDATE_INVENTORY_COSTING = 'P',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id
WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
AND nvl(UPDATE_INVENTORY_COSTING,'N') = 'N';
DPP_UTILITY_PVT.debug_message('Unable to Update the column UPDATE_INVENTORY_COSTING in DPP_TRANSACTION_LINES_ALL Table');
l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
Transaction_number,
org_id,
to_char(Effective_start_date,''YYYY-MM-DD'') Effectivity_date,
vendor_site_id,
Vendor_id,'
||l_user_name|| ' user_name, '
||l_user_id||'user_id,'
||l_exe_dtl_id||'Execution_detail_id,
CURSOR(select transaction_line_id,
inventory_item_id,
supplier_new_price new_price,
UOM,
headers.trx_currency currency
FROM dpp_transaction_lines_all lines
WHERE headers.Transaction_header_id = lines.Transaction_header_id
AND nvl(lines.NOTIFY_PURCHASING_DOCS,''N'') <> ''D'') LINES
FROM dpp_transaction_headers_all headers
WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
'AND EXISTS (SELECT Transaction_header_id
FROM dpp_transaction_lines_all
WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
AND nvl(NOTIFY_PURCHASING_DOCS,''N'') <> ''D'')'
);
UPDATE DPP_TRANSACTION_LINES_ALL
SET notify_purchasing_docs = 'P',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id
WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
AND nvl(notify_purchasing_docs ,'N') <> 'D';
DPP_UTILITY_PVT.debug_message('Unable to Update the column notify_purchasing_docs in DPP_TRANSACTION_LINES_ALL Table');
l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
Transaction_number,
org_id,
Vendor_id,'
||l_user_name|| ' user_name, '
||l_user_id||'user_id,'
||l_exe_dtl_id||'Execution_detail_id,
CURSOR(select transaction_line_id,
inventory_item_id,
supplier_new_price new_price,
UOM,
headers.trx_currency currency
FROM dpp_transaction_lines_all lines
WHERE headers.Transaction_header_id = lines.Transaction_header_id
AND nvl(lines.UPDATE_PURCHASING_DOCS,''N'') = ''N'') LINES
FROM dpp_transaction_headers_all headers
WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
'AND EXISTS (SELECT Transaction_header_id
FROM dpp_transaction_lines_all
WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
AND nvl(UPDATE_PURCHASING_DOCS,''N'') = ''N'')'
);
UPDATE DPP_TRANSACTION_LINES_ALL
SET UPDATE_PURCHASING_DOCS = 'P',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id
WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
AND nvl(UPDATE_PURCHASING_DOCS ,'N')= 'N';
DPP_UTILITY_PVT.debug_message('Unable to Update the column UPDATE_PURCHASING_DOCS in DPP_TRANSACTION_LINES_ALL Table');
SELECT nvl(create_claim_price_increase,'N')
INTO l_price_change_flag
FROM ozf_supp_trd_prfls_all ostp,
dpp_transaction_headers_all dtha
WHERE ostp.supplier_id = to_number(dtha.vendor_id)
AND ostp.supplier_site_id = to_number(dtha.vendor_site_id)
AND ostp.org_id = to_number(dtha.org_id)
AND dtha.transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID;
l_queryCtx := dbms_xmlquery.newContext('SELECT headers.Transaction_header_id,
headers.Transaction_number,
headers.Vendor_id,
headers.org_id,
headers.Vendor_site_id,'
||l_user_name|| ' user_name, '
||l_user_id||'user_id,'
||l_exe_dtl_id||'Execution_detail_id,'
||l_claim_type_flag||'claim_type_flag,
headers.trx_currency,
CURSOR(select dtl.transaction_line_id,
dtl.inventory_item_id,
dtl.claim_amount claim_line_amount,
dtl.approved_inventory CLAIM_QUANTITY,
dtl.UOM,
headers.trx_currency currency
FROM dpp_transaction_lines_all dtl,
DPP_TRANSACTION_LINES_GT dtlg
WHERE headers.Transaction_header_id = dtl.Transaction_header_id
AND dtl.transaction_line_id = dtlg.transaction_line_id
AND '||dtl_price_change||'
AND nvl(dtl.approved_inventory,0) > 0
AND nvl(dtl.SUPP_DIST_CLAIM_STATUS,''N'') = ''N'') LINES
FROM dpp_transaction_headers_all headers
WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
'AND EXISTS (SELECT dtla.Transaction_header_id
FROM dpp_transaction_lines_all dtla,
DPP_TRANSACTION_LINES_GT dtg
WHERE dtla.Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
AND dtla.transaction_line_id = dtg.transaction_line_id
AND nvl(dtla.SUPP_DIST_CLAIM_STATUS,''N'') = ''N''
AND '||dtla_price_change||'
AND nvl(dtla.approved_inventory,0) > 0)'
);
UPDATE DPP_TRANSACTION_LINES_ALL
SET SUPP_DIST_CLAIM_STATUS = 'P',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id
WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
AND nvl(SUPP_DIST_CLAIM_STATUS ,'N')= 'N'
AND nvl(approved_inventory,0) > 0
AND nvl(price_change,0) > 0;
DPP_UTILITY_PVT.debug_message('Unable to Update the column SUPP_DIST_CLAIM_STATUS in DPP_TRANSACTION_LINES_ALL Table');
UPDATE DPP_TRANSACTION_LINES_ALL
SET SUPP_DIST_CLAIM_STATUS = 'Y',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id
WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
AND nvl(SUPP_DIST_CLAIM_STATUS ,'N')= 'N'
AND (nvl(approved_inventory,0) = 0 OR nvl(price_change,0) <= 0);
UPDATE DPP_TRANSACTION_LINES_ALL
SET SUPP_DIST_CLAIM_STATUS = 'P',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id
WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
AND nvl(SUPP_DIST_CLAIM_STATUS ,'N')= 'N'
AND nvl(approved_inventory,0) > 0
AND nvl(price_change,0) <> 0;
DPP_UTILITY_PVT.debug_message('Unable to Update the column SUPP_DIST_CLAIM_STATUS in DPP_TRANSACTION_LINES_ALL Table');
UPDATE DPP_TRANSACTION_LINES_ALL
SET SUPP_DIST_CLAIM_STATUS = 'Y',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id
WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
AND nvl(SUPP_DIST_CLAIM_STATUS ,'N')= 'N'
AND nvl(approved_inventory,0) = 0;
l_queryCtx := dbms_xmlquery.newContext('SELECT headers.Transaction_header_id,
headers.Transaction_number,
headers.org_id,
headers.Vendor_id,
headers.Vendor_site_id,'
||l_user_name|| ' user_name, '
||l_user_id||'user_id,'
||l_exe_dtl_id||'Execution_detail_id,'
||l_claim_type_flag||'claim_type_flag,
headers.trx_currency,
CURSOR(select dcc.customer_inv_line_id TRANSACTION_LINE_ID,
dcc.inventory_item_id,
dcc.cust_account_id CUSTOMER_ACCOUNT_ID,
dcc.supp_claim_amt CLAIM_LINE_AMOUNT,
dcc.reported_inventory claim_quantity,
dcc.trx_currency currency,
dcc.UOM
FROM DPP_customer_claims_all dcc,
DPP_TRANSACTION_LINES_GT dtg
WHERE headers.Transaction_header_id = dcc.Transaction_header_id
AND dcc.customer_inv_line_id = dtg.transaction_line_id
AND nvl(dcc.reported_inventory,0) > 0
AND nvl(dcc.supp_claim_amt,0) > 0
AND nvl(dcc.supplier_claim_created,''N'') = ''N'') LINES
FROM dpp_transaction_headers_all headers
WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
'AND EXISTS (SELECT dcca.Transaction_header_id
FROM DPP_customer_claims_all dcca,
DPP_TRANSACTION_LINES_GT dtga
WHERE dcca.Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
AND dcca.customer_inv_line_id = dtga.transaction_line_id
AND nvl(dcca.supplier_claim_created,''N'') = ''N''
AND nvl(dcca.reported_inventory,0) > 0
AND nvl(dcca.supp_claim_amt,0) > 0)'
);
UPDATE DPP_customer_claims_all
SET supplier_claim_created = 'P',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id
WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
AND nvl(supplier_claim_created,'N') = 'N'
AND nvl(reported_inventory,0) > 0
AND nvl(supp_claim_amt,0) > 0;
DPP_UTILITY_PVT.debug_message('Unable to Update the column supplier_claim_created in DPP_customer_claims_all Table');
UPDATE DPP_customer_claims_all
SET supplier_claim_created = 'Y',
OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1,
last_updated_by = l_user_id,
last_update_date = sysdate,
last_update_login = l_login_id
WHERE transaction_header_id = p_txn_hdr_rec.Transaction_Header_ID
AND nvl(supplier_claim_created,'N') = 'N'
AND (nvl(reported_inventory,0) = 0 OR nvl(supp_claim_amt,0) <= 0);
l_queryCtx := dbms_xmlquery.newContext('SELECT headers.Transaction_header_id,
headers.Transaction_number,
headers.org_id,
headers.Vendor_id,
headers.Vendor_site_id,'
||l_user_name|| ' user_name, '
||l_user_id||'user_id,'
||l_exe_dtl_id||'Execution_detail_id,'
||l_claim_type_flag||'claim_type_flag,
headers.trx_currency,
CURSOR(select dcc.customer_inv_line_id transaction_line_id,
dcc.inventory_item_id,
dcc.cust_account_id customer_account_id,
dcc.cust_claim_amt claim_line_amount,
dcc.reported_inventory claim_quantity,
dcc.trx_currency currency,
dcc.UOM
FROM DPP_customer_claims_all dcc,
DPP_TRANSACTION_LINES_GT dtg
WHERE headers.Transaction_header_id = dcc.Transaction_header_id
AND dcc.customer_inv_line_id = dtg.transaction_line_id
AND nvl(dcc.reported_inventory,0) > 0
AND nvl(dcc.cust_claim_amt,0) > 0
AND nvl(dcc.customer_claim_created,''N'') = ''N'') LINES
FROM dpp_transaction_headers_all headers
WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
'AND EXISTS (SELECT dcca.Transaction_header_id
FROM DPP_customer_claims_all dcca,
DPP_TRANSACTION_LINES_GT dtga
WHERE dcca.Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||'
AND dcca.customer_inv_line_id = dtga.transaction_line_id
AND nvl(dcca.customer_claim_created,''N'') = ''N''
AND nvl(dcca.reported_inventory,0) > 0
AND nvl(dcca.cust_claim_amt,0) > 0)'
);
l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
Transaction_number,
org_id,
nvl(to_char(effective_start_date-days_covered,''YYYY-MM-DD''),''1900-01-01'' ) EFFECTIVE_START_DATE,
to_char(Effective_start_date,''YYYY-MM-DD'') effective_end_date,
trx_currency,'
||l_user_name|| ' user_name, '
||l_user_id||'user_id,'
||l_exe_dtl_id||'Execution_detail_id,
CURSOR(select transaction_line_id,
inventory_item_id,
UOM
FROM dpp_transaction_lines_all lines
WHERE headers.Transaction_header_id = lines.Transaction_header_id
AND lines.price_change > 0 ) LINES
FROM dpp_transaction_headers_all headers
WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
'AND EXISTS (SELECT Transaction_header_id
FROM dpp_transaction_lines_all
WHERE price_change > 0
AND Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||')'
);
l_queryCtx := dbms_xmlquery.newContext('SELECT Transaction_header_id,
Transaction_number,
org_id,
nvl(to_char(effective_start_date-days_covered,''YYYY-MM-DD''),''1900-01-01'' ) EFFECTIVE_START_DATE,
to_char(Effective_start_date,''YYYY-MM-DD'') effective_end_date,'
||l_user_name|| ' user_name, '
||l_user_id||'user_id,'
||l_exe_dtl_id||'Execution_detail_id,
CURSOR(select transaction_line_id,
inventory_item_id
FROM dpp_transaction_lines_all lines
WHERE headers.Transaction_header_id = lines.Transaction_header_id) LINES
FROM dpp_transaction_headers_all headers
WHERE headers.Transaction_header_id = ' ||p_txn_hdr_rec.Transaction_Header_ID||
'AND EXISTS (SELECT Transaction_header_id
FROM dpp_transaction_lines_all
WHERE Transaction_header_id = '||p_txn_hdr_rec.Transaction_Header_ID||')'
);
INSERT INTO DPP_EXECUTION_DETAILS (EXECUTION_DETAIL_ID,
object_version_number,
TRANSACTION_HEADER_ID,
PROCESS_CODE,
INPUT_XML,
EXECUTION_STATUS,
EXECUTION_START_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN)
VALUES (p_exe_dtl_id,
1,
p_txn_hdr_id,
p_process_code,
XMLTYPE(p_input_xml),
l_exe_status,
sysdate,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_user_id);