[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_InterfaceErrSts(
p_txn_header_rec_type IN OUT nocopy txn_header_rec,
x_return_status OUT nocopy VARCHAR2
)
AS
l_api_name constant VARCHAR2(30) := 'Update_InterfaceErrSts';
FND_FILE.PUT_LINE(FND_FILE.LOG,' Begin Update InterfaceErrSts');
UPDATE DPP_TXN_HEADERS_INT_ALL dtha
SET
dtha.last_update_date = SYSDATE,
dtha.last_updated_by = nvl(l_user_id,0),
dtha.last_update_login = nvl(l_user_id,0),
dtha.interface_status = 'E',
dtha.error_code = decode(dtha.error_code,NULL,nvl(p_txn_header_rec_type.error_code,'SQL_PLSQL_ERROR'),'MULTIPLE_ERRORS')
WHERE transaction_int_header_id = p_txn_header_rec_type.transaction_int_header_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,' End Update InterfaceErrSts');
END Update_InterfaceErrSts;
SELECT fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL') into l_value from dual;
SELECT hr.organization_id
,hr.name
INTO p_txn_header_rec_type.org_id
,p_txn_header_rec_type.operating_unit_name
FROM hr_operating_units hr
WHERE hr.name = nvl(p_txn_header_rec_type.operating_unit_name,hr.name) AND
hr.organization_id =nvl(to_number(p_txn_header_rec_type.org_id),hr.organization_id)
AND mo_global.check_access(hr.organization_id) = 'Y' ;
SELECT ap.vendor_id
,ap.vendor_name
INTO p_txn_header_rec_type.vendor_id
,p_txn_header_rec_type.vendor_name
FROM ap_suppliers ap
WHERE (ap.vendor_name = NVL(p_txn_header_rec_type.vendor_name,ap.vendor_name) AND
ap.vendor_id = NVL(to_number(p_txn_header_rec_type.vendor_id),ap.vendor_id))
AND ap.enabled_flag = 'Y'
AND ap.hold_flag = 'N'
AND TRUNC(sysdate) >= nvl(TRUNC(start_date_active), TRUNC(sysdate))
AND TRUNC(sysdate) < nvl(TRUNC(end_date_active), TRUNC(sysdate) + 1);
SELECT apssa.vendor_site_id
,apssa.vendor_site_code
INTO p_txn_header_rec_type.vendor_site_id
,p_txn_header_rec_type.vendor_site_code
FROM ap_supplier_sites_all apssa,
ozf_supp_trd_prfls_all ostp
WHERE apssa.vendor_id = to_number(p_txn_header_rec_type.vendor_id)
AND ostp.supplier_id = apssa.vendor_id
AND ostp.supplier_site_id = apssa.vendor_site_id
AND ostp.org_id = apssa.org_id
AND nvl(apssa.rfq_only_site_flag, 'N') = 'N'
AND nvl(apssa.inactive_date, l_trunc_sysdate + 1) > l_trunc_sysdate
AND (apssa.vendor_site_code = nvl(p_txn_header_rec_type.vendor_site_code,apssa.vendor_site_code)
AND apssa.vendor_site_id = nvl(to_number(p_txn_header_rec_type.vendor_site_id), apssa.vendor_site_id))
AND apssa.org_id = to_number(p_txn_header_rec_type.org_id);
SELECT COUNT(dtlia.supplier_part_num)
INTO l_duplicate_lines
FROM dpp_txn_headers_int_all dthia,
dpp_txn_lines_int_all dtlia
WHERE dthia.transaction_int_header_id = dtlia.transaction_int_header_id
AND dthia.transaction_int_header_id = p_txn_header_rec_type.transaction_int_header_id
GROUP BY dtlia.supplier_part_num
HAVING COUNT(dtlia.supplier_part_num) > 1;
SELECT COUNT(dtlia.item_number)
INTO l_duplicate_lines
FROM dpp_txn_headers_int_all dthia,
dpp_txn_lines_int_all dtlia
WHERE dthia.transaction_int_header_id = dtlia.transaction_int_header_id
AND dthia.transaction_int_header_id = p_txn_header_rec_type.transaction_int_header_id
GROUP BY dtlia.item_number
HAVING COUNT(dtlia.item_number) > 1;
SELECT COUNT(dtlia.inventory_item_id)
INTO l_duplicate_lines
FROM dpp_txn_headers_int_all dthia,
dpp_txn_lines_int_all dtlia
WHERE dthia.transaction_int_header_id = dtlia.transaction_int_header_id
AND dthia.transaction_int_header_id = p_txn_header_rec_type.transaction_int_header_id
GROUP BY dtlia.inventory_item_id
HAVING COUNT(dtlia.inventory_item_id) > 1;
SELECT inventory_organization_id
INTO p_txn_header_rec_type.inventory_organization_id
FROM financials_system_params_all
WHERE org_id = to_number(p_txn_header_rec_type.org_id);
SELECT gs.currency_code
INTO p_txn_header_rec_type.functional_currency
FROM gl_sets_of_books gs
, hr_operating_units hr
WHERE hr.set_of_books_id = gs.set_of_books_id
AND hr.organization_id = p_txn_header_rec_type.org_id;
Update_InterfaceErrSts(
p_txn_header_rec_type => p_txn_header_rec_type
,x_return_status =>x_return_status
);
SELECT 1 INTO l_lines FROM
dpp_txn_lines_int_all dtla WHERE
dtla.transaction_int_header_id = p_txn_header_rec_type.transaction_int_header_id;
SELECT default_days_covered
INTO p_txn_header_rec_type.days_covered
FROM ozf_supp_trd_prfls_all ostpa
WHERE ostpa.supplier_id =to_number(p_txn_header_rec_type.vendor_id)
AND ostpa.supplier_site_id =to_number(p_txn_header_rec_type.vendor_site_id);
SELECT currency_code
INTO p_txn_header_rec_type.trx_currency
FROM fnd_currencies
WHERE currency_flag = 'Y'
AND enabled_flag = 'Y'
AND currency_code =p_txn_header_rec_type.trx_currency;
SELECT dpp_transaction_hdr_id_seq.nextval
INTO p_txn_header_rec_type.transaction_header_id
FROM dual;
SELECT dpp_transaction_number_seq.nextval
INTO p_txn_header_rec_type.transaction_number
FROM dual;
SELECT transaction_number
INTO l_transaction_number
FROM dpp_transaction_headers_all dtha
WHERE dtha.transaction_number = p_txn_header_rec_type.transaction_number;
SELECT lookup_code
INTO p_txn_header_rec_type.transaction_status
FROM fnd_lookups
WHERE lookup_type = 'DPP_TRANSACTION_STATUSES'
AND lookup_code = 'NEW';
FND_FILE.PUT_LINE(FND_FILE.LOG,' Inserting Transaction Header');
INSERT INTO DPP_TRANSACTION_HEADERS_ALL(
object_version_number
,transaction_header_id
,transaction_number
,ref_document_number
,vendor_id
,vendor_contact_id
,vendor_contact_name
,contact_email_address
,contact_phone
,vendor_site_id
,transaction_source
,effective_start_date
,days_covered
,transaction_status
,org_id
,orig_sys_document_ref
,creation_date
,last_refreshed_by
,last_refreshed_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,request_id
,program_application_id
,program_id
,program_update_date
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute16
,attribute17
,attribute18
,attribute19
,attribute20
,attribute21
,attribute22
,attribute23
,attribute24
,attribute25
,attribute26
,attribute27
,attribute28
,attribute29
,attribute30
,trx_currency)
VALUES(
1.0
,p_txn_header_rec_type.transaction_header_id
,p_txn_header_rec_type.transaction_number
,p_txn_header_rec_type.ref_document_number
,p_txn_header_rec_type.vendor_id
,p_txn_header_rec_type.vendor_contact_id
,p_txn_header_rec_type.vendor_contact_name
,p_txn_header_rec_type.contact_email_address
,p_txn_header_rec_type.contact_phone
,p_txn_header_rec_type.vendor_site_id
,p_txn_header_rec_type.transaction_source
,TRUNC(p_txn_header_rec_type.effective_start_date)
,p_txn_header_rec_type.days_covered
,p_txn_header_rec_type.transaction_status
,p_txn_header_rec_type.org_id
,p_txn_header_rec_type.orig_sys_document_ref
,sysdate--creation_date
,null--last_refreshed_by
,NULL--last_refreshed_date
,NVL(l_user_id,0)
,SYSDATE
,NVL(l_user_id,0)
,NVL(l_user_id,0)
,p_txn_header_rec_type.REQUEST_ID
,p_txn_header_rec_type.PROGRAM_APPLICATION_ID
,p_txn_header_rec_type.PROGRAM_ID
,p_txn_header_rec_type.PROGRAM_UPDATE_DATE
,p_txn_header_rec_type.ATTRIBUTE_CATEGORY
,p_txn_header_rec_type.ATTRIBUTE1
,p_txn_header_rec_type.ATTRIBUTE2
,p_txn_header_rec_type.ATTRIBUTE3
,p_txn_header_rec_type.ATTRIBUTE4
,p_txn_header_rec_type.ATTRIBUTE5
,p_txn_header_rec_type.ATTRIBUTE6
,p_txn_header_rec_type.ATTRIBUTE7
,p_txn_header_rec_type.ATTRIBUTE8
,p_txn_header_rec_type.ATTRIBUTE9
,p_txn_header_rec_type.ATTRIBUTE10
,p_txn_header_rec_type.ATTRIBUTE11
,p_txn_header_rec_type.ATTRIBUTE12
,p_txn_header_rec_type.ATTRIBUTE13
,p_txn_header_rec_type.ATTRIBUTE14
,p_txn_header_rec_type.ATTRIBUTE15
,p_txn_header_rec_type.ATTRIBUTE16
,p_txn_header_rec_type.ATTRIBUTE17
,p_txn_header_rec_type.ATTRIBUTE18
,p_txn_header_rec_type.ATTRIBUTE19
,p_txn_header_rec_type.ATTRIBUTE20
,p_txn_header_rec_type.ATTRIBUTE21
,p_txn_header_rec_type.ATTRIBUTE22
,p_txn_header_rec_type.ATTRIBUTE23
,p_txn_header_rec_type.ATTRIBUTE24
,p_txn_header_rec_type.ATTRIBUTE25
,p_txn_header_rec_type.ATTRIBUTE26
,p_txn_header_rec_type.ATTRIBUTE27
,p_txn_header_rec_type.ATTRIBUTE28
,p_txn_header_rec_type.ATTRIBUTE29
,p_txn_header_rec_type.ATTRIBUTE30
,p_txn_header_rec_type.trx_currency);
SELECT dpp_transaction_line_id_seq.nextval
INTO p_txn_lines_tbl_type(i).transaction_line_id
FROM dual;
SELECT lookup_code
INTO p_txn_lines_tbl_type(i).line_status
FROM fnd_lookups
WHERE lookup_code = 'NEW'
AND lookup_type = 'DPP_TRANSACTION_STATUSES';
SELECT msi.inventory_item_id,
msi.concatenated_segments,
msi.primary_uom_code
INTO p_txn_lines_tbl_type(i).inventory_item_id
,p_txn_lines_tbl_type(i).item_number
,p_txn_lines_tbl_type(i).uom
FROM mtl_system_items_kfv msi
WHERE msi.purchasing_item_flag = 'Y'
AND msi.shippable_item_flag = 'Y'
AND msi.enabled_flag = 'Y'
AND NVL(msi.consigned_flag,2) = 2 -- 2=unconsigned
AND msi.mtl_transactions_enabled_flag = 'Y'
AND msi.organization_id = p_txn_header_rec_type.inventory_organization_id
AND msi.concatenated_segments = p_txn_lines_tbl_type(i).item_number
AND msi.inventory_item_id = to_number(p_txn_lines_tbl_type(i).inventory_item_id)
AND msi.primary_uom_code = nvl(p_txn_lines_tbl_type(i).uom,msi.primary_uom_code);
SELECT msi.inventory_item_id,
msi.concatenated_segments,
msi.primary_uom_code
INTO p_txn_lines_tbl_type(i).inventory_item_id
,p_txn_lines_tbl_type(i).item_number
,p_txn_lines_tbl_type(i).uom
FROM mtl_system_items_kfv msi
WHERE msi.purchasing_item_flag = 'Y'
AND msi.shippable_item_flag = 'Y'
AND msi.enabled_flag = 'Y'
AND NVL(msi.consigned_flag,2) = 2 -- 2=unconsigned
AND msi.mtl_transactions_enabled_flag = 'Y'
AND msi.organization_id = p_txn_header_rec_type.inventory_organization_id
AND msi.inventory_item_id = to_number(p_txn_lines_tbl_type(i).inventory_item_id)
AND msi.primary_uom_code = nvl(p_txn_lines_tbl_type(i).uom,msi.primary_uom_code);
SELECT msi.inventory_item_id,
msi.concatenated_segments,
msi.primary_uom_code
INTO p_txn_lines_tbl_type(i).inventory_item_id
,p_txn_lines_tbl_type(i).item_number
,p_txn_lines_tbl_type(i).uom
FROM mtl_system_items_kfv msi
WHERE msi.purchasing_item_flag = 'Y'
AND msi.shippable_item_flag = 'Y'
AND msi.enabled_flag = 'Y'
AND NVL(msi.consigned_flag,2) = 2 -- 2=unconsigned
AND msi.mtl_transactions_enabled_flag = 'Y'
AND msi.organization_id = p_txn_header_rec_type.inventory_organization_id
AND msi.concatenated_segments = p_txn_lines_tbl_type(i).item_number
AND msi.primary_uom_code = nvl(p_txn_lines_tbl_type(i).uom,msi.primary_uom_code);
SELECT occ.external_code
INTO p_txn_lines_tbl_type(i).supplier_part_num
FROM ozf_supp_trd_prfls_all ostpa,
ozf_supp_code_conversions_all occ
WHERE occ.internal_code = p_txn_lines_tbl_type(i).inventory_item_id
AND occ.code_conversion_type = 'OZF_PRODUCT_CODES'
AND occ.org_id = to_number(p_txn_header_rec_type.org_id)
AND occ.supp_trade_profile_id = ostpa.supp_trade_profile_id
AND ostpa.supplier_id = to_number(p_txn_header_rec_type.vendor_id)
AND ostpa.supplier_site_id = to_number(p_txn_header_rec_type.vendor_site_id)
AND (trunc(sysdate) between occ.start_date_active and occ.end_date_active OR occ.end_date_active is null)
AND occ.external_code =p_txn_lines_tbl_type(i).supplier_part_num;
SELECT occ.external_code
INTO p_txn_lines_tbl_type(i).supplier_part_num
FROM ozf_supp_trd_prfls_all ostpa,
ozf_supp_code_conversions_all occ
WHERE occ.internal_code = p_txn_lines_tbl_type(i).inventory_item_id
AND occ.code_conversion_type = 'OZF_PRODUCT_CODES'
AND occ.org_id = to_number(p_txn_header_rec_type.org_id)
AND occ.supp_trade_profile_id = ostpa.supp_trade_profile_id
AND ostpa.supplier_id = to_number(p_txn_header_rec_type.vendor_id)
AND ostpa.supplier_site_id = to_number(p_txn_header_rec_type.vendor_site_id)
AND (trunc(sysdate) between occ.start_date_active and occ.end_date_active OR occ.end_date_active is null);
SELECT occ.external_code
,occ.internal_code
INTO p_txn_lines_tbl_type(i).supplier_part_num
,p_txn_lines_tbl_type(i).inventory_item_id
FROM ozf_supp_trd_prfls_all ostpa,
ozf_supp_code_conversions_all occ
WHERE occ.code_conversion_type = 'OZF_PRODUCT_CODES'
AND occ.org_id = to_number(p_txn_header_rec_type.org_id)
AND occ.supp_trade_profile_id = ostpa.supp_trade_profile_id
AND ostpa.supplier_id = to_number(p_txn_header_rec_type.vendor_id)
AND ostpa.supplier_site_id = to_number(p_txn_header_rec_type.vendor_site_id)
AND (trunc(sysdate) between occ.start_date_active and occ.end_date_active OR occ.end_date_active is null)
AND occ.external_code =p_txn_lines_tbl_type(i).supplier_part_num;
--get list price for the selected inventory organization id
SELECT msi.LIST_PRICE_PER_UNIT list_price
INTO p_txn_lines_tbl_type(i).list_price
FROM mtl_system_items msi
WHERE msi.inventory_item_id = to_number(p_txn_lines_tbl_type(i).inventory_item_id) and
msi.organization_id = p_txn_header_rec_type.inventory_organization_id;
SELECT fnd_profile.VALUE('DPP_NEW_PRICE_DECIMAL_PRECISION')
INTO l_rounding
FROM dual;
INSERT INTO dpp_transaction_lines_all(
object_version_number
, transaction_header_id
, transaction_line_id
, supplier_part_num
, line_number
, prior_price
, change_type
, change_value
, price_change
, covered_inventory
, approved_inventory
, uom
, org_id
, orig_sys_document_line_ref
, creation_date
, created_by
, last_update_date
, last_updated_by
, last_update_login
, request_id
, program_application_id
, program_id
, program_update_date
, attribute_category
, attribute1
, attribute2
, attribute3
, attribute4
, attribute5
, attribute6
, attribute7
, attribute8
, attribute9
, attribute10
, attribute11
, attribute12
, attribute13
, attribute14
, attribute15
, attribute16
, attribute17
, attribute18
, attribute19
, attribute20
, attribute21
, attribute22
, attribute23
, attribute24
, attribute25
, attribute26
, attribute27
, attribute28
, attribute29
, attribute30
, inventory_item_id
, supplier_new_price
, last_calculated_by
, last_calculated_date
, claim_amount
, supp_dist_claim_id
, update_purchasing_docs
, notify_purchasing_docs
, update_inventory_costing
, update_item_list_price
, supp_dist_claim_status
, onhand_inventory
, manually_adjusted
, notify_inbound_pricelist
, notify_outbound_pricelist
,notify_promotions_pricelist)
VALUES(
1.0
, p_txn_lines_tbl_type(i).transaction_header_id
, p_txn_lines_tbl_type(i).transaction_line_id
, p_txn_lines_tbl_type(i).supplier_part_num
, p_txn_lines_tbl_type(i).line_number
, ROUND(nvl(p_txn_lines_tbl_type(i).prior_price,0),l_rounding)
, p_txn_lines_tbl_type(i).change_type
, ROUND(p_txn_lines_tbl_type(i).change_value,l_rounding)
, ROUND(p_txn_lines_tbl_type(i).price_change,l_rounding)
, p_txn_lines_tbl_type(i).covered_inventory
, p_txn_lines_tbl_type(i).approved_inventory
, p_txn_lines_tbl_type(i).uom
, p_txn_lines_tbl_type(i).org_id
, p_txn_lines_tbl_type(i).orig_sys_document_line_ref
, sysdate --
, NVL(l_user_id,0) --created_by
, sysdate --last_update_date
, NVL(l_user_id,0)--p_txn_lines_tbl_type(i).last_updated_by
, NVL(l_user_id,0)--p_txn_lines_tbl_type(i).last_update_login
, p_txn_lines_tbl_type(i).request_id --request_id
, p_txn_lines_tbl_type(i).program_application_id --program_application_id
, p_txn_lines_tbl_type(i).program_id --program_id
, p_txn_lines_tbl_type(i).program_update_date --program_update_date
, p_txn_lines_tbl_type(i).attribute_category --attribute_category
, p_txn_lines_tbl_type(i).attribute1 --attribute1
, p_txn_lines_tbl_type(i).attribute2 --attribute2
, p_txn_lines_tbl_type(i).attribute3 --attribute3
, p_txn_lines_tbl_type(i).attribute4 --attribute4
, p_txn_lines_tbl_type(i).attribute5 --attribute5
, p_txn_lines_tbl_type(i).attribute6 --attribute6
, p_txn_lines_tbl_type(i).attribute7 --attribute7
, p_txn_lines_tbl_type(i).attribute8 --attribute8
, p_txn_lines_tbl_type(i).attribute9 --attribute9
, p_txn_lines_tbl_type(i).attribute10 --attribute10
, p_txn_lines_tbl_type(i).attribute11 --attribute11
, p_txn_lines_tbl_type(i).attribute12 --attribute12
, p_txn_lines_tbl_type(i).attribute13 --attribute13
, p_txn_lines_tbl_type(i).attribute14 --attribute14
, p_txn_lines_tbl_type(i).attribute15 --attribute15
, p_txn_lines_tbl_type(i).attribute16 --attribute16
, p_txn_lines_tbl_type(i).attribute17 --attribute17
, p_txn_lines_tbl_type(i).attribute18 --attribute18
, p_txn_lines_tbl_type(i).attribute19 --attribute19
, p_txn_lines_tbl_type(i).attribute20 --attribute20
, p_txn_lines_tbl_type(i).attribute21 --attribute21
, p_txn_lines_tbl_type(i).attribute22 --attribute22
, p_txn_lines_tbl_type(i).attribute23 --attribute23
, p_txn_lines_tbl_type(i).attribute24 --attribute24
, p_txn_lines_tbl_type(i).attribute25 --attribute25
, p_txn_lines_tbl_type(i).attribute26 --attribute26
, p_txn_lines_tbl_type(i).attribute27 --attribute27
, p_txn_lines_tbl_type(i).attribute28 --attribute28
, p_txn_lines_tbl_type(i).attribute29 --attribute29
, p_txn_lines_tbl_type(i).attribute30 --attribute30
, p_txn_lines_tbl_type(i).inventory_item_id
, p_txn_lines_tbl_type(i).supplier_new_price
, null --p_txn_lines_tbl_type(i).last_calculated_by
, null --p_txn_lines_tbl_type(i).last_calculated_date
, null --p_txn_lines_tbl_type(i).claim_amount
, null --p_txn_lines_tbl_type(i).supp_dist_claim_id
, 'N' --p_txn_lines_tbl_type(i).update_purchasing_docs
, 'N' --p_txn_lines_tbl_type(i).notify_purchasing_docs
, 'N' --p_txn_lines_tbl_type(i).update_inventory_costing
, 'N' --p_txn_lines_tbl_type(i).update_item_list_price
, 'N' --p_txn_lines_tbl_type(i).supp_dist_claim_status
, null--p_txn_lines_tbl_type(i).onhand_inventory
, 'N'--p_txn_lines_tbl_type(i).notify_purchasing_docs
, 'N' --p_txn_lines_tbl_type(i).notify_inbound_pricelist
, 'N'--p_txn_lines_tbl_type(i).notify_outbound_pricelist
, 'N'--p_txn_lines_tbl_type(i).notify_promotions_pricelist
);
PROCEDURE Update_CoveredInv(
p_txn_header_rec_type IN OUT nocopy txn_header_rec
,p_txn_lines_tbl_type IN OUT nocopy txn_lines_tbl
,x_msg_count OUT nocopy NUMBER
,x_msg_data OUT nocopy VARCHAR2
,x_return_status OUT nocopy VARCHAR2)
AS
l_api_name constant VARCHAR2(30) := 'Update_CoveredInv';
SAVEPOINT DPP_Update_CoveredInv;
FND_FILE.PUT_LINE(FND_FILE.LOG,' Begin Update Covered Inventory');
l_header_rec_type.last_updated_by :=p_txn_header_rec_type.last_updated_by;
FND_FILE.PUT_LINE(FND_FILE.LOG,' Update_CoveredInv Rec type Transaction Header Id: '||l_header_rec_type.transaction_header_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,' Update_CoveredInv Rec type Org_ID'||l_header_rec_type.org_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,' Update_CoveredInv Rec type Effective Start Date'|| l_header_rec_type.effective_start_date);
FND_FILE.PUT_LINE(FND_FILE.LOG,' Update_CoveredInv Rec type Effective End Date'||l_header_rec_type.effective_end_date);
FND_FILE.PUT_LINE(FND_FILE.LOG,' Update_CoveredInv TBL type Transaction Line Id'||l_line_tbl_type(i).transaction_line_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,' Update_CoveredInv TBL type Inventory Item ID'||l_line_tbl_type(i).inventory_item_id);
FND_FILE.PUT_LINE(FND_FILE.LOG,' Update_CoveredInv TBL type UOM Code'||l_line_tbl_type(i).uom_code);
DPP_COVEREDINVENTORY_PVT.Select_CoveredInventory(
p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,p_commit => l_commit
,p_validation_level => l_validation_level
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_inv_hdr_rec => l_header_rec_type
,p_covered_inv_tbl => l_line_tbl_type
);
dpp_coveredinventory_pvt.Update_CoveredInventory(
p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,p_commit => l_commit
,p_validation_level => l_validation_level
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_inv_hdr_rec => l_header_rec_type
,p_covered_inv_tbl => l_line_tbl_type
);
FND_FILE.PUT_LINE(FND_FILE.LOG,' End Update Covered Inventory');
UPDATE dpp_transaction_lines_all dtla
SET dtla.approved_inventory = dtla.covered_inventory,
dtla.object_version_number = dtla.object_version_number +1,
dtla.last_updated_by = nvl(l_user_id,0),
dtla.last_update_login = nvl(l_user_id,0),
dtla.last_update_date = sysdate
WHERE dtla.transaction_header_id = p_txn_header_rec_type.transaction_header_id
AND dtla.transaction_line_id = l_line_tbl_type(i).transaction_line_id;
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_header_rec_type.transaction_header_id;
UPDATE dpp_transaction_lines_all dtla
SET dtla.claim_amount = dtla.approved_inventory * price_change,
dtla.object_version_number = dtla.object_version_number +1,
dtla.last_updated_by = nvl(l_user_id,0),
dtla.last_update_login = nvl(l_user_id,0),
dtla.last_update_date = sysdate
WHERE dtla.transaction_header_id = p_txn_header_rec_type.transaction_header_id
AND dtla.transaction_line_id = l_line_tbl_type(i).transaction_line_id
AND dtla.price_change > 0;
UPDATE dpp_transaction_lines_all dtla
SET dtla.claim_amount = dtla.approved_inventory * price_change,
dtla.object_version_number = dtla.object_version_number +1,
dtla.last_updated_by = nvl(l_user_id,0),
dtla.last_update_login = nvl(l_user_id,0),
dtla.last_update_date = sysdate
WHERE dtla.transaction_header_id = p_txn_header_rec_type.transaction_header_id
AND dtla.transaction_line_id = l_line_tbl_type(i).transaction_line_id
AND dtla.price_change <> 0;
p_txn_header_rec_type.error_code := 'DPP_UPDATE_COVEREDINV';
ROLLBACK TO DPP_Update_CoveredInv;
p_txn_header_rec_type.error_code := 'DPP_UPDATE_COVEREDINV';
ROLLBACK TO DPP_Update_CoveredInv;
ROLLBACK TO DPP_Update_CoveredInv;
END Update_CoveredInv;
PROCEDURE Update_Approval(
p_txn_header_rec_type IN OUT nocopy txn_header_rec
,p_txn_lines_tbl_type IN OUT nocopy txn_lines_tbl
,x_msg_count OUT nocopy NUMBER
,x_msg_data OUT nocopy VARCHAR2
,x_return_status OUT nocopy VARCHAR2)
AS
l_api_name constant VARCHAR2(30) := 'Update_Approval';
FND_FILE.PUT_LINE(FND_FILE.LOG,' Begin Update Approval');
SELECT dtla.supp_dist_claim_id
INTO p_txn_lines_tbl_type(i).supp_dist_claim_id
FROM dpp_transaction_lines_all dtla,
ozf_claims_all oca
WHERE dtla.transaction_header_id = p_txn_header_rec_type.transaction_header_id
AND to_number(dtla.supp_dist_claim_id) = oca.claim_id
AND (oca.claim_number = nvl(p_txn_header_rec_type.supp_dist_claim_number,oca.claim_number)
AND oca.claim_id = nvl(to_number(p_txn_header_rec_type.supp_dist_claim_id),oca.claim_id))
AND dtla.inventory_item_id = p_txn_lines_tbl_type(i).inventory_item_id
AND dtla.supplier_approved_by IS NULL;
UPDATE dpp_transaction_headers_all dtha
SET dtha.last_updated_by = nvl(l_user_id,0),
dtha.last_update_login = nvl(l_user_id,0),
dtha.last_update_date = sysdate,
dtha.object_version_number = dtha.object_version_number +1
WHERE dtha.ref_document_number = p_txn_header_rec_type.ref_document_number
AND dtha.vendor_id = p_txn_header_rec_type.vendor_id;
SELECT dtla.supplier_approved_by
INTO l_approved_by
FROM dpp_transaction_lines_all dtla
WHERE dtla.transaction_header_id = p_txn_lines_tbl_type(i).transaction_header_id
AND dtla.inventory_item_id = p_txn_lines_tbl_type(i).inventory_item_id;
UPDATE dpp_transaction_lines_all dtla
SET dtla.supplier_approved_by =p_txn_lines_tbl_type(i).supplier_approved_by,
dtla.supplier_approval_date =TRUNC(p_txn_lines_tbl_type(i).supplier_approval_date),
dtla.approved_inventory = p_txn_lines_tbl_type(i).approved_inventory,
dtla.claim_amount= p_txn_lines_tbl_type(i).approved_inventory*price_change,
dtla.last_updated_by = nvl(l_user_id,0),
dtla.last_update_login = nvl(l_user_id,0),
dtla.last_update_date = sysdate,
dtla.object_version_number = dtla.object_version_number +1
WHERE dtla.transaction_header_id = p_txn_lines_tbl_type(i).transaction_header_id
AND dtla.inventory_item_id =p_txn_lines_tbl_type(i).inventory_item_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,' End Update Approval');
END Update_Approval;
PROCEDURE Update_ClaimsApproval(
p_txn_header_rec_type IN OUT nocopy txn_header_rec
,p_txn_lines_tbl_type IN OUT nocopy txn_lines_tbl
,x_msg_count OUT nocopy NUMBER
,x_msg_data OUT nocopy VARCHAR2
,x_return_status OUT nocopy VARCHAR2)
AS
l_api_name constant VARCHAR2(30) := 'Update_ClaimsApproval';
FND_FILE.PUT_LINE(FND_FILE.LOG,' Begin Update Claims Approval');
SELECT fl.lookup_code
INTO l_claim_txn_hdr_rec_type.Process_code
FROM fnd_lookups fl
WHERE fl.lookup_type = 'DPP_EXECUTION_PROCESSES'
AND fl.lookup_code = 'UPDCLM';
SELECT count(dtla.supplier_approved_by)
INTO l_count_approved_lines
FROM dpp_transaction_lines_all dtla
WHERE dtla.transaction_header_id = p_txn_header_rec_type.transaction_header_id
AND dtla.supp_dist_claim_id = p_txn_lines_tbl_type(i).supp_dist_claim_id
AND dtla.supplier_approved_by IS NOT NULL;
FND_FILE.PUT_LINE(FND_FILE.LOG,' Begin Update Claims Approval l_count_approved_lines:'
||l_count_approved_lines);
SELECT count(transaction_line_id)
INTO l_count_lines
FROM dpp_transaction_lines_all dtla
WHERE transaction_header_id = p_txn_header_rec_type.transaction_header_id
AND dtla.supp_dist_claim_id = p_txn_lines_tbl_type(i).supp_dist_claim_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,' Begin Update Claims Approval l_count_lines:'
||l_count_lines);
UPDATE dpp_transaction_claims_all dtca
SET dtca.approved_by_supplier = 'Y',
dtca.last_updated_by = nvl(l_user_id,0),
dtca.last_update_date =sysdate,
dtca.last_update_login = nvl(l_user_id,0),
dtca.object_version_number = dtca.object_version_number +1
WHERE dtca.transaction_header_id = p_txn_header_rec_type.transaction_header_id
AND dtca.claim_id = p_txn_lines_tbl_type(i).supp_dist_claim_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,' Raises Business Event to Update claims:'
||l_count_lines);
||' update the claim status.');
FND_FILE.PUT_LINE(FND_FILE.LOG,' End Update Claims Approval');
END Update_ClaimsApproval;
SELECT *
FROM dpp_txn_lines_int_all
WHERE transaction_int_header_id = p_transaction_id
order by transaction_int_line_id;
l_txn_lines_rec_type.last_update_date := sysdate;
l_txn_lines_rec_type.last_updated_by := nvl(l_user_id,0);
l_txn_lines_rec_type.last_update_login := nvl(l_user_id,0);
l_txn_lines_rec_type.program_update_date := fetch_lines_rec.program_update_date;
PROCEDURE Update_HeaderLog(
p_txn_header_rec_type IN OUT nocopy txn_header_rec
,x_msg_count OUT nocopy NUMBER
,x_msg_data OUT nocopy VARCHAR2
,x_return_status OUT nocopy VARCHAR2)
AS
l_api_name constant VARCHAR2(30) := 'Update_HeaderLog';
FND_FILE.PUT_LINE(FND_FILE.LOG,' Begin Update HeaderLog');
l_txn_hdr_hist_rec.last_update_date := p_txn_header_rec_type.last_update_date;
l_txn_hdr_hist_rec.last_updated_by := p_txn_header_rec_type.last_updated_by;
l_txn_hdr_hist_rec.last_update_login := p_txn_header_rec_type.last_update_login;
dpp_log_pvt.insert_headerlog(
p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,p_commit => l_commit
,p_validation_level => l_validation_level
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_txn_hdr_rec => l_txn_hdr_hist_rec
);
FND_FILE.PUT_LINE(FND_FILE.LOG,' End Update HeaderLog');
END Update_HeaderLog;
PROCEDURE Update_LinesLog(
p_txn_lines_tbl_type IN OUT nocopy txn_lines_tbl
,x_msg_count OUT nocopy NUMBER
,x_msg_data OUT nocopy VARCHAR2
,x_return_status OUT nocopy VARCHAR2)
AS
l_api_name constant VARCHAR2(30) := 'Update_LinesLog';
FND_FILE.PUT_LINE(FND_FILE.LOG,' Begin Update LinesLog');
l_txn_line_hist_tbl(i).last_update_date := p_txn_lines_tbl_type(i).last_update_date;
l_txn_line_hist_tbl(i).last_updated_by := p_txn_lines_tbl_type(i).last_updated_by;
l_txn_line_hist_tbl(i).last_update_login := p_txn_lines_tbl_type(i).last_update_login;
l_txn_line_hist_tbl(i).update_purchasing_docs := p_txn_lines_tbl_type(i).update_purchasing_docs;
l_txn_line_hist_tbl(i).update_inventory_costing := p_txn_lines_tbl_type(i).update_inventory_costing ;
l_txn_line_hist_tbl(i).update_item_list_price := p_txn_lines_tbl_type(i).update_item_list_price;
dpp_log_pvt.Insert_LinesLog(
p_api_version => l_api_version
,p_init_msg_list => l_init_msg_list
,p_commit => l_commit
,p_validation_level => l_validation_level
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_txn_lines_tbl => l_txn_line_hist_tbl
);
FND_FILE.PUT_LINE(FND_FILE.LOG,' End Update LinesLog');
END Update_LinesLog;
PROCEDURE Update_InterfaceTbl(
p_txn_header_rec_type IN OUT nocopy txn_header_rec
, p_txn_lines_tbl_type IN OUT nocopy txn_lines_tbl
, x_msg_count OUT nocopy NUMBER
, x_msg_data OUT nocopy VARCHAR2
, x_return_status OUT nocopy VARCHAR2)
AS
l_date DATE;
l_api_name constant VARCHAR2(30) := 'Update_InterfaceTbl';
FND_FILE.PUT_LINE(FND_FILE.LOG,' Begin Update InterfaceTbl');
UPDATE DPP_TXN_HEADERS_INT_ALL dtha
SET
dtha.org_id = p_txn_header_rec_type.org_id,
dtha.vendor_id = p_txn_header_rec_type.vendor_id,
dtha.vendor_site_id = p_txn_header_rec_type.vendor_site_id,
dtha.last_update_date = SYSDATE,
dtha.last_updated_by = nvl(l_user_id,0),
dtha.last_update_login = nvl(l_user_id,0),
dtha.interface_status = 'P',
dtha.currency =p_txn_header_rec_type.trx_currency
WHERE TRANSACTION_INT_HEADER_ID = p_txn_header_rec_type.transaction_int_header_id;
UPDATE DPP_TXN_LINES_INT_ALL dtla
SET
dtla.supplier_part_num = p_txn_lines_tbl_type(i).supplier_part_num,
dtla.inventory_item_id = p_txn_lines_tbl_type(i).inventory_item_id,
dtla.item_number = p_txn_lines_tbl_type(i).item_number,
dtla.interface_status = 'P',--p_txn_lines_tbl_type(i).interface_status,
dtla.org_id = p_txn_lines_tbl_type(i).org_id,
dtla.last_update_date = SYSDATE,
dtla.last_updated_by = nvl(l_user_id,0),
dtla.last_update_login = nvl(l_user_id,0)
WHERE dtla.transaction_int_line_id = p_txn_lines_tbl_type(i).transaction_int_line_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,' End Update InterfaceTbl');
END Update_InterfaceTbl;
PROCEDURE Update_InterfaceLineErrSts(
p_txn_header_rec_type IN OUT nocopy txn_header_rec,
p_txn_lines_tbl_type IN OUT NOCOPY txn_lines_tbl,
x_return_status OUT nocopy VARCHAR2
)
AS
l_api_name constant VARCHAR2(30) := 'Update_InterfaceErrSts';
FND_FILE.PUT_LINE(FND_FILE.LOG,' Begin Update InterfaceErrSts');
UPDATE DPP_TXN_HEADERS_INT_ALL dtha
SET
dtha.last_update_date = SYSDATE,
dtha.last_updated_by = nvl(l_user_id,0),
dtha.last_update_login = nvl(l_user_id,0),
dtha.interface_status = 'E',
dtha.error_code = decode(dtha.error_code,NULL,nvl(p_txn_header_rec_type.error_code,'SQL_PLSQL_ERROR'),'MULTIPLE_ERRORS')
WHERE transaction_int_header_id = p_txn_header_rec_type.transaction_int_header_id;
UPDATE DPP_TXN_LINES_INT_ALL dtla
SET
dtla.last_update_date = SYSDATE,
dtla.last_updated_by = nvl(l_user_id,0),
dtla.last_update_login = nvl(l_user_id,0),
dtla.interface_status = 'E',
dtla.error_code = p_txn_lines_tbl_type(i).error_code
WHERE dtla.transaction_int_header_id = p_txn_header_rec_type.transaction_int_header_id
AND dtla.transaction_int_line_id = p_txn_lines_tbl_type(i).transaction_int_line_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,' End Update InterfaceErrSts');
END Update_InterfaceLineErrSts;
PROCEDURE Insert_Transaction(p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 := fnd_api.g_false
, p_commit IN VARCHAR2 := fnd_api.g_false
, p_validation_level IN NUMBER := fnd_api.g_valid_level_full
, p_transaction_int_header_id IN NUMBER
, p_operating_unit IN VARCHAR2 DEFAULT NULL
, x_return_status OUT nocopy VARCHAR2
, x_msg_count OUT nocopy NUMBER
, x_msg_data OUT nocopy VARCHAR2
)
IS
l_api_name constant VARCHAR2(30) := 'insert_transaction';
SELECT *
FROM dpp_txn_headers_int_all dthia
WHERE dthia.transaction_int_header_id = p_transaction_int_header_id;
SAVEPOINT DPP_Insert_Transaction;
fnd_file.put_line(fnd_file.log, 'Begin Insert Transaction ' );
SELECT fnd_profile.VALUE('DPP_AUDIT_ENABLED')
INTO l_log_enabled
FROM dual;
dpp_utility_pvt.debug_message('Insert Transaction:'||l_transaction_type);
l_txn_header_rec_type.last_update_date := sysdate;
l_txn_header_rec_type.last_updated_by := nvl(l_user_id,0);
l_txn_header_rec_type.last_update_login := nvl(l_user_id,0);
l_txn_header_rec_type.program_update_date := fetch_header_rec.program_update_date;
Update_InterfaceErrSts(
p_txn_header_rec_type => l_txn_header_rec_type
,x_return_status =>l_return_status
);
ROLLBACK TO DPP_Insert_Transaction;
Update_InterfaceErrSts(
p_txn_header_rec_type => l_txn_header_rec_type
,x_return_status =>l_return_status
);
ROLLBACK TO DPP_Insert_Transaction;
Update_InterfaceErrSts(
p_txn_header_rec_type => l_txn_header_rec_type
,x_return_status =>l_return_status
);
Update_InterfaceErrSts(
p_txn_header_rec_type => l_txn_header_rec_type
,x_return_status =>l_return_status
);
ROLLBACK TO DPP_Insert_Transaction;
Update_InterfaceErrSts(
p_txn_header_rec_type => l_txn_header_rec_type
,x_return_status =>l_return_status
);
SELECT dtha.transaction_status,
dtha.transaction_header_id
INTO l_transaction_status
,l_txn_header_rec_type.transaction_header_id
FROM dpp_transaction_headers_all dtha
WHERE dtha.ref_document_number = l_txn_header_rec_type.ref_document_number
AND dtha.vendor_id = l_txn_header_rec_type.vendor_id
--Modification to include supplier site reference
AND dtha.vendor_site_id = l_txn_header_rec_type.vendor_site_id
AND transaction_status <> 'CANCELLED';
Update_InterfaceErrSts(
p_txn_header_rec_type => l_txn_header_rec_type
,x_return_status =>l_return_status
);
SELECT lookup_code
INTO l_status_cancelled
FROM fnd_lookups
WHERE lookup_type = 'DPP_TRANSACTION_STATUSES'
AND lookup_code = 'CANCELLED';
Update_InterfaceErrSts(
p_txn_header_rec_type => l_txn_header_rec_type
,x_return_status =>l_return_status
);
ROLLBACK TO DPP_Insert_Transaction;
Update_InterfaceErrSts(
p_txn_header_rec_type => l_txn_header_rec_type
,x_return_status =>l_return_status
);
Update_HeaderLog(p_txn_header_rec_type => l_txn_header_rec_type
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => l_return_status
);
fnd_file.put_line(fnd_file.log, 'Update Header Log Return Status:'||l_return_status ||'Error Msg'||x_msg_data);
Update_InterfaceLineErrSts(
p_txn_header_rec_type => l_txn_header_rec_type,
p_txn_lines_tbl_type => l_txn_lines_tbl_type
,x_return_status =>l_return_status
);
ROLLBACK TO DPP_Insert_Transaction;
Update_InterfaceLineErrSts(
p_txn_header_rec_type => l_txn_header_rec_type,
p_txn_lines_tbl_type => l_txn_lines_tbl_type
,x_return_status =>l_return_status
);
ROLLBACK TO DPP_Insert_Transaction;
Update_InterfaceLineErrSts(
p_txn_header_rec_type => l_txn_header_rec_type,
p_txn_lines_tbl_type => l_txn_lines_tbl_type
,x_return_status =>l_return_status
);
Update_LinesLog(
p_txn_lines_tbl_type => l_txn_lines_tbl_type
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => l_return_status
);
fnd_file.put_line(fnd_file.log, 'Update Lines Log Return Status:'||l_return_status ||'Error Msg'||x_msg_data);
Update_InterfaceLineErrSts(
p_txn_header_rec_type => l_txn_header_rec_type,
p_txn_lines_tbl_type => l_txn_lines_tbl_type
,x_return_status =>l_return_status
);
Update_CoveredInv(
p_txn_header_rec_type => l_txn_header_rec_type
,p_txn_lines_tbl_type => l_txn_lines_tbl_type
,x_msg_count => x_msg_count
,x_msg_data =>x_msg_data
,x_return_status => l_return_status
);
dpp_utility_pvt.debug_message('Update CoveredInv Status :'||l_return_status);
fnd_file.put_line(fnd_file.log, 'Update Covered Inventory Return Status:'||l_return_status);
ROLLBACK TO DPP_Insert_Transaction;
Update_InterfaceLineErrSts(
p_txn_header_rec_type => l_txn_header_rec_type,
p_txn_lines_tbl_type => l_txn_lines_tbl_type
,x_return_status =>l_return_status
);
Update_InterfaceErrSts(
p_txn_header_rec_type => l_txn_header_rec_type
,x_return_status =>l_return_status
);
Update_InterfaceErrSts(
p_txn_header_rec_type => l_txn_header_rec_type
,x_return_status =>l_return_status
);
Update_InterfaceErrSts(
p_txn_header_rec_type => l_txn_header_rec_type
,x_return_status =>l_return_status
);
ROLLBACK TO DPP_Insert_Transaction;
Update_InterfaceLineErrSts(
p_txn_header_rec_type => l_txn_header_rec_type,
p_txn_lines_tbl_type => l_txn_lines_tbl_type
,x_return_status =>l_return_status
);
Update_InterfaceLineErrSts(
p_txn_header_rec_type => l_txn_header_rec_type,
p_txn_lines_tbl_type => l_txn_lines_tbl_type
,x_return_status =>l_return_status
);
Update_Approval(
p_txn_header_rec_type =>l_txn_header_rec_type
,p_txn_lines_tbl_type => l_txn_lines_tbl_type
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => l_return_status
);
fnd_file.put_line(fnd_file.log, 'Update Approval Return Status:'||l_return_status ||'Error Msg'||x_msg_data);
ROLLBACK TO DPP_Insert_Transaction;
Update_InterfaceLineErrSts(
p_txn_header_rec_type => l_txn_header_rec_type,
p_txn_lines_tbl_type => l_txn_lines_tbl_type
,x_return_status =>l_return_status
);
Update_InterfaceErrSts(
p_txn_header_rec_type => l_txn_header_rec_type
,x_return_status =>l_return_status
);
Update_HeaderLog(p_txn_header_rec_type => l_txn_header_rec_type
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => l_return_status
);
fnd_file.put_line(fnd_file.log, 'Update Header Log Return Status:'||l_return_status ||'Error Msg'||x_msg_data);
Update_LinesLog(
p_txn_lines_tbl_type => l_txn_lines_tbl_type
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => l_return_status
);
fnd_file.put_line(fnd_file.log, 'Update Lines Log Return Status:'||l_return_status ||'Error Msg'||x_msg_data);
Update_InterfaceErrSts(
p_txn_header_rec_type => l_txn_header_rec_type
,x_return_status =>l_return_status
);
Update_ClaimsApproval (
p_txn_header_rec_type =>l_txn_header_rec_type
,p_txn_lines_tbl_type => l_txn_lines_tbl_type
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_return_status => l_return_status
);
fnd_file.put_line(fnd_file.log, 'Update Claims Approval Return Status:'||l_return_status ||'Error Msg'||x_msg_data);
ROLLBACK TO DPP_Insert_Transaction;
Update_InterfaceErrSts(
p_txn_header_rec_type => l_txn_header_rec_type
,x_return_status =>l_return_status
);
Update_InterfaceTbl (
p_txn_header_rec_type =>l_txn_header_rec_type
,p_txn_lines_tbl_type => l_txn_lines_tbl_type
,x_msg_count => x_msg_count
,x_msg_data =>x_msg_data
,x_return_status => l_return_status
);
fnd_file.put_line(fnd_file.log, 'Update Interface Table Return Status: '||l_return_status ||'Error Msg '||x_msg_data);
ROLLBACK TO DPP_Insert_Transaction;
Update_InterfaceErrSts(
p_txn_header_rec_type => l_txn_header_rec_type
,x_return_status =>l_return_status
);
Update_InterfaceErrSts(
p_txn_header_rec_type => l_txn_header_rec_type
,x_return_status =>l_return_status
);
END insert_transaction;
SELECT transaction_int_header_id
INTO l_transaction_int_header_id
FROM dpp_txn_headers_int_all dtha
WHERE dtha.ref_document_number = p_txn_header_rec_type.ref_document_number
AND dtha.vendor_name = p_txn_header_rec_type.vendor_name
AND dtha.vendor_site = p_txn_header_rec_type.vendor_site_code
AND dtha.interface_status = 'N';
SELECT dpp_trans_int_hdr_id_seq.nextval
INTO p_txn_header_rec_type.transaction_int_header_id
FROM dual;
INSERT INTO dpp_txn_headers_int_all
(transaction_int_header_id,
ref_document_number,
effective_start_date,
days_covered,
org_id,
operating_unit_name,
vendor_name,
vendor_id,
vendor_site,
vendor_site_id,
vendor_contact_name,
contact_email_address,
contact_phone,
currency,
supp_dist_claim_id,
supp_dist_claim_number,
supplier_approved_by,
supplier_approval_date,
transaction_source,
interface_status,
error_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30)
VALUES(
p_txn_header_rec_type.transaction_int_header_id,
p_txn_header_rec_type.ref_document_number,
p_txn_header_rec_type.effective_start_date,
p_txn_header_rec_type.days_covered,
NULL,
p_txn_header_rec_type.operating_unit_name,
p_txn_header_rec_type.vendor_name,
NULL,
p_txn_header_rec_type.vendor_site_code,
NULL,
p_txn_header_rec_type.vendor_contact_name,
p_txn_header_rec_type.contact_email_address,
p_txn_header_rec_type.contact_phone,
p_txn_header_rec_type.trx_currency,
NULL,
p_txn_header_rec_type.SUPP_DIST_CLAIM_NUMBER,
p_txn_header_rec_type.supplier_approved_by,
p_txn_header_rec_type.supplier_approval_date,
p_txn_header_rec_type.transaction_source,
'N',
NULL,
sysdate,
FND_GLOBAL.User_Id ,
sysdate,
FND_GLOBAL.User_Id ,
FND_GLOBAL.User_Id ,
0,
0,
0,
sysdate,
p_txn_header_rec_type.attribute_category,
p_txn_header_rec_type.attribute1,
p_txn_header_rec_type.attribute2,
p_txn_header_rec_type.attribute3,
p_txn_header_rec_type.attribute4,
p_txn_header_rec_type.attribute5,
p_txn_header_rec_type.attribute6,
p_txn_header_rec_type.attribute7,
p_txn_header_rec_type.attribute8,
p_txn_header_rec_type.attribute9,
p_txn_header_rec_type.attribute10,
p_txn_header_rec_type.attribute11,
p_txn_header_rec_type.attribute12,
p_txn_header_rec_type.attribute13,
p_txn_header_rec_type.attribute14,
p_txn_header_rec_type.attribute15,
p_txn_header_rec_type.attribute16,
p_txn_header_rec_type.attribute17,
p_txn_header_rec_type.attribute18,
p_txn_header_rec_type.attribute19,
p_txn_header_rec_type.attribute20,
p_txn_header_rec_type.attribute21,
p_txn_header_rec_type.attribute22,
p_txn_header_rec_type.attribute23,
p_txn_header_rec_type.attribute24,
p_txn_header_rec_type.attribute25,
p_txn_header_rec_type.attribute26,
p_txn_header_rec_type.attribute27,
p_txn_header_rec_type.attribute28,
p_txn_header_rec_type.attribute29,
p_txn_header_rec_type.attribute30);
UPDATE dpp_txn_headers_int_all
SET
ref_document_number = p_txn_header_rec_type.ref_document_number,
effective_start_date = p_txn_header_rec_type.effective_start_date,
days_covered = p_txn_header_rec_type.days_covered,
vendor_name = p_txn_header_rec_type.vendor_name,
vendor_site = p_txn_header_rec_type.vendor_site_code,
VENDOR_CONTACT_NAME=p_txn_header_rec_type.VENDOR_CONTACT_NAME,
contact_email_address = p_txn_header_rec_type.contact_email_address,
contact_phone = p_txn_header_rec_type.contact_phone,
attribute1 = p_txn_header_rec_type.attribute1,
attribute2 = p_txn_header_rec_type.attribute2,
attribute3 = p_txn_header_rec_type.attribute3,
attribute4 = p_txn_header_rec_type.attribute4,
attribute5 = p_txn_header_rec_type.attribute5,
attribute6 = p_txn_header_rec_type.attribute6,
attribute7 = p_txn_header_rec_type.attribute7,
attribute8 = p_txn_header_rec_type.attribute8,
attribute9 = p_txn_header_rec_type.attribute9,
attribute10 = p_txn_header_rec_type.attribute10,
attribute11 = p_txn_header_rec_type.attribute11,
attribute12 = p_txn_header_rec_type.attribute12,
attribute13 = p_txn_header_rec_type.attribute13,
attribute14 = p_txn_header_rec_type.attribute14,
attribute15 = p_txn_header_rec_type.attribute15,
attribute16 = p_txn_header_rec_type.attribute16,
attribute17 = p_txn_header_rec_type.attribute17,
attribute18 = p_txn_header_rec_type.attribute18,
attribute19 = p_txn_header_rec_type.attribute19,
attribute20 = p_txn_header_rec_type.attribute20,
attribute21 = p_txn_header_rec_type.attribute21,
attribute22 = p_txn_header_rec_type.attribute22,
attribute23 = p_txn_header_rec_type.attribute23,
attribute24 = p_txn_header_rec_type.attribute24,
attribute25 = p_txn_header_rec_type.attribute25,
attribute26 = p_txn_header_rec_type.attribute26,
attribute27 = p_txn_header_rec_type.attribute27,
attribute28 = p_txn_header_rec_type.attribute28,
attribute29 = p_txn_header_rec_type.attribute29,
attribute30 = p_txn_header_rec_type.attribute30
WHERE transaction_int_header_id = l_transaction_int_header_id;
SELECT dpp_trans_int_line_id_seq.nextval
INTO p_transaction_lines_rec.transaction_int_line_id
FROM dual;
INSERT INTO dpp_txn_lines_int_all(
transaction_int_header_id,
transaction_int_line_id,
supplier_part_num,
inventory_item_id,
item_number,
change_type,
change_value,
covered_inventory,
approved_inventory,
uom,
claim_amount,
org_id,
interface_status,
error_code,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute16,
attribute17,
attribute18,
attribute19,
attribute20,
attribute21,
attribute22,
attribute23,
attribute24,
attribute25,
attribute26,
attribute27,
attribute28,
attribute29,
attribute30)
VALUES(
p_transaction_lines_rec.transaction_int_header_id,
p_transaction_lines_rec.transaction_int_line_id,
p_transaction_lines_rec.supplier_part_num,
null,
p_transaction_lines_rec.item_number,
p_transaction_lines_rec.change_type,
p_transaction_lines_rec.change_value,
p_transaction_lines_rec.covered_inventory,
p_transaction_lines_rec.approved_inventory,
p_transaction_lines_rec.uom,
NULL,
NULL,
'N',
NULL,
sysdate,
FND_GLOBAL.User_Id ,
sysdate,
FND_GLOBAL.User_Id ,
FND_GLOBAL.User_Id ,
NULL,
NULL,
NULL,
NULL,
p_transaction_lines_rec.attribute_category,
p_transaction_lines_rec.attribute1,
p_transaction_lines_rec.attribute2,
p_transaction_lines_rec.attribute3,
p_transaction_lines_rec.attribute4,
p_transaction_lines_rec.attribute5,
p_transaction_lines_rec.attribute6,
p_transaction_lines_rec.attribute7,
p_transaction_lines_rec.attribute8,
p_transaction_lines_rec.attribute9,
p_transaction_lines_rec.attribute10,
p_transaction_lines_rec.attribute11,
p_transaction_lines_rec.attribute12,
p_transaction_lines_rec.attribute13,
p_transaction_lines_rec.attribute14,
p_transaction_lines_rec.attribute15,
p_transaction_lines_rec.attribute16,
p_transaction_lines_rec.attribute17,
p_transaction_lines_rec.attribute18,
p_transaction_lines_rec.attribute19,
p_transaction_lines_rec.attribute20,
p_transaction_lines_rec.attribute21,
p_transaction_lines_rec.attribute22,
p_transaction_lines_rec.attribute23,
p_transaction_lines_rec.attribute24,
p_transaction_lines_rec.attribute25,
p_transaction_lines_rec.attribute26,
p_transaction_lines_rec.attribute27,
p_transaction_lines_rec.attribute28,
p_transaction_lines_rec.attribute29,
p_transaction_lines_rec.attribute30);
SELECT transaction_int_header_id
FROM dpp_txn_headers_int_all dthia
WHERE dthia.ref_document_number = cv_doc_ref_no
AND dthia.vendor_name = cv_supplier_name
AND dthia.vendor_site = cv_supplier_site
AND dthia.interface_status = 'N'
AND dthia.operating_unit_name = cv_operating_unit;
DPP_TRANSACTION_Pvt.Insert_Transaction(
p_api_version =>l_api_version
, p_init_msg_list =>l_init_msg_list
, p_commit =>l_commit
, p_validation_level =>l_validation_level
, p_transaction_int_header_id =>l_transaction_int_header_id
, p_operating_unit => p_operating_unit
, x_return_status =>x_return_status
, x_msg_count =>l_msg_count
, x_msg_data =>x_msg_data
) ;
SELECT transaction_int_header_id
FROM dpp_txn_headers_int_all dtha ,
hr_operating_units hr
WHERE dtha.interface_status = 'N'
AND nvl(dtha.ref_document_number, -1) = nvl(cv_doc_ref_no,nvl(dtha.ref_document_number,-1))
AND (dtha.vendor_name = cv_supplier_name OR
dtha.vendor_id = cv_supplier_id)
AND (nvl(dtha.vendor_site,-1) = nvl(cv_supplier_site, nvl(dtha.vendor_site,-1)) OR
nvl(dtha.vendor_site_id,-1) = nvl(cv_supplier_site_id, nvl(dtha.vendor_site_id,-1)))
AND (nvl(dtha.operating_unit_name,-1) = nvl(cv_operating_unit, nvl(dtha.operating_unit_name,-1))
OR nvl(dtha.org_id,-1) = nvl(cv_org_id, nvl( dtha.org_id,-1)))
AND hr.name = nvl(dtha.operating_unit_name,hr.name)
AND hr.organization_id =nvl(to_number(dtha.org_id),hr.organization_id)
AND mo_global.check_access(hr.organization_id) = 'Y'
ORDER BY transaction_int_header_id;
SELECT fnd_profile.value('XLA_MO_SECURITY_PROFILE_LEVEL') into l_value from dual;
SELECT vendor_id
INTO l_supplier_id
FROM ap_suppliers
WHERE vendor_name = p_supplier_name;
SELECT vendor_site_id
INTO l_supplier_site_id
FROM ap_supplier_sites_all
WHERE vendor_id = to_number(l_supplier_id)
AND vendor_site_code =p_supplier_site;
SELECT organization_id
INTO l_org_id
FROM hr_operating_units
WHERE name = p_operating_unit;
fnd_file.put_line(fnd_file.log, 'Call Insert Transaction p_transaction_int_header_id:'||transaction_rec.transaction_int_header_id);
UPDATE dpp_txn_headers_int_all dtha
SET dtha.program_id = l_program_id,
dtha.program_application_id = l_prog_app_id,
dtha.request_id = l_request_id,
dtha.program_update_date = sysdate
WHERE dtha.transaction_int_header_id = transaction_rec.transaction_int_header_id;
UPDATE dpp_txn_lines_int_all dtla
SET dtla.program_id = l_program_id,
dtla.program_application_id = l_prog_app_id,
dtla.request_id = l_request_id,
dtla.program_update_date = sysdate
WHERE dtla.transaction_int_header_id = transaction_rec.transaction_int_header_id;
dpp_utility_pvt.debug_message('Call Insert Transaction p_transaction_int_header_id:'||transaction_rec.transaction_int_header_id);
DPP_TRANSACTION_PVT.Insert_Transaction(
p_api_version =>l_api_version
, p_init_msg_list =>l_init_msg_list
, p_commit =>l_commit
, p_validation_level =>l_validation_level
, p_transaction_int_header_id =>transaction_rec.transaction_int_header_id
, p_operating_unit => p_operating_unit
, x_return_status =>l_return_status
, x_msg_count =>l_msg_count
, x_msg_data =>l_msg_data
) ;
FND_FILE.PUT_LINE(FND_FILE.LOG,'No Records selected for Processing');