[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
CURSOR c_chr (b_chr_id NUMBER) IS SELECT
authoring_org_id
,currency_code
,date_signed approved_date
,po_headers_interface_s.NEXTVAL po_interface_header_id
,DECODE(contract_number_modifier, null,
contract_number,
contract_number || ' - ' || contract_number_modifier)
contract_number_print -- used to form the
-- comments in the PO Header
-- ,po_headers_s.NEXTVAL po_header_id
FROM okc_k_headers_b
WHERE id = b_chr_id;
SELECT
cle.id source_cle_id -- source for the PO line
,cim.object1_id1 -- item_id
,cim.uom_code -- uom_code
,cim.number_of_items qty -- quantity
,cle.price_negotiated/cim.number_of_items -- unit_price
,cle.price_unit -- list_price_per_unit
,trunc(greatest(cle.start_date, sysdate)) need_by_date
-- need by date cannot be earlier than sysdate according to a check
-- performed in PDOI. Logged bug 2166158 for this
FROM
okc_k_lines_b cle,
okc_statuses_b sts,
okc_k_items cim
-- jtf_objects_b jot -- (Sangeeta) not necessary to check the source
WHERE
cim.cle_id = cle.id
--AND jot.object_code = cim.jtot_object1_code
AND sts.code = cle.sts_code
AND cle.dnz_chr_id = b_chr_id
AND sts.code in (g_sts_signed, g_sts_active) -- only active and signed lines
AND cle.cle_id IS NULL -- only top lines
AND cle.price_level_ind = 'Y' -- indicates this is a priced line
AND cle.item_to_price_yn = 'Y' -- indicates the item comes from inventory
ORDER BY cle.display_sequence;
G_LAST_UPDATE_LOGIN NUMBER;
SELECT user_name
INTO l_k_buyer_name
FROM FND_USER
WHERE EMPLOYEE_ID = (SELECT cco.object1_id1 agent_id
FROM okc_contacts cco
WHERE cco.cro_code = 'BUYER'
AND cco.jtot_object1_code = 'OKX_BUYER'
AND cco.dnz_chr_id = p_chr_id
AND rownum = 1)
AND rownum = 1;
b_cle_id NUMBER) IS SELECT
rgp.chr_id
,rgp.cle_id
,rul.object1_id1
,rul.object1_id2
,rul.jtot_object1_code
,rul.object2_id1
,rul.object2_id2
,rul.jtot_object2_code
,rul.rule_information_category
,rul.rule_information1
FROM okc_rule_groups_b rgp
,okc_rules_b rul
WHERE
rgp.dnz_chr_id = b_chr_id
AND rul.rgp_id = rgp.id
AND ((rgp.cle_id IS NULL AND b_cle_id IS NULL) OR
(b_cle_id IS NOT NULL AND rgp.cle_id = b_cle_id))
AND ((rgp.rgd_code = g_rg_billing and rul.rule_information_category = g_ru_billto) or
(rgp.rgd_code = g_rg_shipping and rul.rule_information_category = g_ru_shipto) or
(rgp.rgd_code = g_rg_payment and rul.rule_information_category = g_ru_payto) or
(rgp.rgd_code = g_rg_payment and rul.rule_information_category = g_ru_payterms) or
(rgp.rgd_code = g_rg_currency and rul.rule_information_category = g_ru_convert));
SELECT conversion_type,
conversion_rate,
conversion_date
FROM okc_conversion_attribs_v
WHERE conversion_type = b_id1
AND dnz_chr_id = p_chr_id;
my_debug('40: Rule selected: '||g_ru_payto, 1);
my_debug('80: Rule selected: '|| g_ru_billto, 1);
my_debug('120: Rule selected: '|| g_ru_convert, 1);
my_debug('200: Rule selected: '|| g_ru_payterms, 1);
my_debug('240: Rule selected: '|| g_ru_payterms, 1);
my_debug('280: Rule selected: '|| g_ru_shipto, 1);
my_debug('320: Rule selected: '|| g_ru_shipto, 1);
my_debug('380: Rules selection: '||l_ru_h_nb||' rule(s) selected at header level', 1);
my_debug('400: Rules selection: '||l_ru_l_nb||' rule(s) selected at line level', 1);
SELECT cco.object1_id1 agent_id
INTO x_po_header_rec.agent_id
FROM okc_contacts cco
WHERE cco.cro_code = 'BUYER'
AND cco.jtot_object1_code = 'OKX_BUYER'
AND cco.dnz_chr_id = p_chr_id
AND rownum = 1; -- added - just in case there is more than one contact defined
SELECT cpr.object1_id1 vendor_id,
cpr.id
INTO x_po_header_rec.vendor_id,
l_cpr_id
FROM okc_k_party_roles_b cpr
WHERE
cpr.rle_code = 'VENDOR'
AND cpr.jtot_object1_code = 'OKX_VENDOR'
AND cpr.cle_id IS NULL -- header level vendors only
AND cpr.dnz_chr_id = p_chr_id;
SELECT cco.object1_id1 vendor_contact_id
INTO x_po_header_rec.vendor_contact_id
FROM okc_contacts cco
WHERE cco.cpl_id = l_cpr_id
AND cco.dnz_chr_id = p_chr_id
AND cco.jtot_object1_code = 'OKX_VCONTACT'
AND rownum = 1; -- added - just in case there is more than one contact defined
SELECT po_lines_interface_s.NEXTVAL
INTO x_po_lines_tab(l_idx).interface_line_id
FROM dual;
SELECT expense_account
INTO x_po_dist_tab(l_idx).charge_account_id
FROM okx_system_items_v
WHERE inventory_item_id = x_po_lines_tab(l_idx).interface_line_id
AND organization_id = SYS_CONTEXT('OKC_CONTEXT','ORGANIZATION_ID');
PROCEDURE insert_po_hdr(
p_chr_id IN NUMBER
,p_batch_id IN NUMBER
,p_po_header_rec IN po_header_rec_type
,x_return_status OUT NOCOPY VARCHAR2
,x_po_header_id OUT NOCOPY po_headers_all.po_header_id%TYPE) IS
l_po_null_rec po_header_rec_type; -- initialize to null
okc_debug.Set_Indentation('insert_po_hdr');
my_debug('1020: Entering insert_po_hdr', 2);
SELECT po_headers_s.nextval
INTO l_po_header_id
FROM dual;
INSERT INTO po_headers_interface
(
batch_id,
interface_header_id,
interface_source_code,
process_code,
action,
org_id,
document_type_code,
-- document_num, -- will not be providing for phase I as the setup in demo env. will be set to manual and numbering is numeric
po_header_id,
currency_code,
rate_type_code,
rate_date,
rate,
agent_id,
vendor_id,
vendor_site_id,
vendor_contact_id,
ship_to_location_id,
bill_to_location_id,
terms_id,
freight_terms,
approval_status,
approved_date,
acceptance_required_flag,
frozen_flag,
approval_required_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
comments
)
VALUES
(
p_batch_id, -- batch_id
p_po_header_rec.interface_header_id, -- interface_header_id
g_po_hdr_interface_source_code, -- interface_source_code
g_po_hdr_process_code, -- process_code
g_po_hdr_action, -- action
p_po_header_rec.org_id, -- org_id
g_po_hdr_document_type_code, -- document_type_code
-- p_po_header_rec.document_num, -- document_num
l_po_header_id, -- po_header_id
p_po_header_rec.currency_code, -- currency_code
p_po_header_rec.rate_type_code, -- rate_type_code
p_po_header_rec.rate_date, -- rate_date
p_po_header_rec.rate, -- rate
p_po_header_rec.agent_id, -- agent_id
p_po_header_rec.vendor_id, -- vendor_id
p_po_header_rec.vendor_site_id, -- vendor_site_id
p_po_header_rec.vendor_contact_id, -- vendor_contact_id
p_po_header_rec.ship_to_location_id, -- ship_to_location_id
p_po_header_rec.bill_to_location_id, -- bill_to_location_id
p_po_header_rec.terms_id, -- terms_id
p_po_header_rec.freight_terms, -- freight_terms
g_po_hdr_approval_status, -- approval_status
p_po_header_rec.approved_date, -- approved_date
g_po_hdr_accept_required_flag, -- acceptance_required_flag
g_po_hdr_frozen_flag, -- frozen_flag
g_po_hdr_approv_required_flag, -- approval_required_flag
sysdate, -- creation_date
G_USER_ID, -- created_by
sysdate, -- last_update_date
G_USER_ID, -- last_updated_by
G_LAST_UPDATE_LOGIN, -- last_update_login
G_REQUEST_ID, -- request_id
G_PROGRAM_APPLICATION_ID, -- program_application_id
G_PROGRAM_ID, -- program_id
sysdate, -- program_update_date
p_po_header_rec.comments -- comments
);
INSERT INTO OKC_K_REL_OBJS
( id,
cle_id,
chr_id,
rty_code,
object1_id1,
object1_id2,
jtot_object1_code,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
okc_p_util.raw_to_number(sys_guid()) -- id
,null -- cle_id
,p_chr_id -- chr_id
,g_crj_rty_code -- rty_code
,l_po_header_id -- object1_id1
,'#' -- object1_id2
,g_crj_chr_jtot_object_code -- jtot_object1_code
,1 -- object_version_number
,G_USER_ID -- created_by
,sysdate -- creation_date
,G_USER_ID -- last_updated_by
,sysdate -- last_update_date
,g_last_update_login -- last_update_login
);
my_debug('1080: Inserted rows into OKC_K_REL_OBJS for Header: ' || sql%ROWCOUNT , 4);
my_debug('1100: Exiting insert_po_hdr', 2);
my_debug('1140: Exiting insert_po_hdr', 4);
END insert_po_hdr;
PROCEDURE insert_po_lines(p_chr_id IN okc_k_headers_b.id%TYPE
,p_po_lines_tab IN po_lines_tab
,p_po_header_id IN po_lines_all.po_header_id%TYPE
,x_return_status OUT NOCOPY VARCHAR2) IS
-- Declaration of individual elements to avoid ORA-3113 error because
-- FORALL does not allow insert of elements of %rowtype
ls_freight_terms Char30TabTyp;
okc_debug.Set_Indentation('insert_po_lines');
my_debug('1160: Entering insert_po_lines', 2);
my_debug('1500: G_LAST_UPDATE_LOGIN = ' || G_LAST_UPDATE_LOGIN,2);
INSERT INTO PO_LINES_INTERFACE
(
interface_line_id,
interface_header_id,
line_num,
-- po_line_id, -- uncomment if we are able to pass po_line_id
shipment_type,
item_id,
uom_code,
quantity,
unit_price,
list_price_per_unit,
ship_to_organization_id,
ship_to_location_id,
need_by_date,
freight_terms,
last_update_date,
last_updated_by,
last_update_login,
creation_date,
created_by,
request_id,
program_application_id,
program_id,
program_update_date
)
VALUES
(
ls_interface_line_id(i), -- interface_line_id
ls_interface_header_id(i), -- interface_header_id
ls_line_num(i), -- line_num
--null, -- po_line_id
g_po_ln_shipment_type, -- shipment_type
ls_item_id(i), -- item_id
ls_uom_code(i), -- uom_code
ls_quantity(i), -- quantity
ls_unit_price(i), -- unit_price
ls_list_price_per_unit(i), -- list_price_per_unit
ls_ship_to_organization_id(i), -- ship_to_organization_id
ls_ship_to_location_id(i), -- ship_to_location_id
ls_need_by_date(i), -- need_by_date
ls_freight_terms(i), -- freight_terms
sysdate, -- last_update_date
G_USER_ID, -- last_updated_by
G_LAST_UPDATE_LOGIN, -- last_update_login
sysdate, -- creation_date
G_USER_ID, -- created_by
G_REQUEST_ID, -- request_id
G_PROGRAM_APPLICATION_ID, -- program_application_id
G_PROGRAM_ID, -- program_id
sysdate -- program_update_date
);
INSERT INTO OKC_K_REL_OBJS
( id,
cle_id,
chr_id,
rty_code,
object1_id1,
object1_id2,
jtot_object1_code,
object_version_number,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
)
VALUES
(
okc_p_util.raw_to_number(sys_guid()) -- id
,ls_source_cle_id(i) -- cle_id
,p_chr_id -- chr_id
,g_crj_rty_code -- rty_code
,p_po_header_id -- object1_id1 -- see note above
,ls_line_num(i) -- object1_id2 -- see note above
,g_crj_cle_jtot_object_code -- jtot_object1_code
,1 -- object_version_number
,G_USER_ID -- created_by
,sysdate -- creation_date
,G_USER_ID -- last_updated_by
,sysdate -- last_update_date
,g_last_update_login -- last_update_login
);
ls_interface_line_id.delete;
ls_interface_header_id.delete;
ls_line_num.delete;
ls_po_line_id.delete;
ls_item_id.delete;
ls_uom_code.delete;
ls_quantity.delete;
ls_unit_price.delete;
ls_list_price_per_unit.delete;
ls_ship_to_organization_id.delete;
ls_ship_to_location_id.delete;
ls_need_by_date.delete;
ls_freight_terms.delete;
ls_source_cle_id.delete;
my_debug('1560: Exiting insert_po_lines', 2);
my_debug('1600: Exiting insert_po_lines', 4);
END insert_po_lines;
PROCEDURE insert_po_distributions(p_po_dist_tab IN po_distributions_tab
,x_return_status OUT NOCOPY VARCHAR2 ) IS
-- Declaration of individual elements to avoid ORA-3113 error because
-- FORALL does not allow insert of elements of %rowtype
ls_interface_header_id NumberTabTyp;
okc_debug.Set_Indentation('insert_po_distributions');
my_debug('1620: Entering insert_po_distributions', 2);
INSERT INTO PO_DISTRIBUTIONS_INTERFACE
(
interface_header_id,
interface_line_id,
interface_distribution_id,
distribution_num,
org_id,
quantity_ordered,
destination_type_code,
charge_account_id
)
VALUES
(
ls_interface_header_id(i), -- interface_header_id
ls_interface_line_id(i), -- interface_line_id
po_distributions_interface_s.NEXTVAL, -- interface_distribution_id
1, -- distribution_num
ls_org_id(i), -- org_id
ls_quantity_ordered(i), -- quantity_ordered
g_po_dis_destination_type_code, -- destination_type_code
ls_charge_account_id(i) -- charge_account_id
);
ls_org_id.delete;
ls_quantity_ordered.delete;
my_debug('1700: Exiting insert_po_distributions', 2);
my_debug('1740: Exiting insert_po_distributions', 4);
END insert_po_distributions;
G_LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
SELECT NVL(MAX(batch_id),0) + 1
INTO l_batch_id
FROM po_headers_interface;
insert_po_hdr(
p_chr_id => p_chr_id
,p_batch_id => l_batch_id
,p_po_header_rec => l_po_header_rec
,x_return_status => l_return_status
,x_po_header_id => l_po_header_id);
insert_po_lines(p_chr_id => p_chr_id
,p_po_lines_tab => l_po_lines_tab
,p_po_header_id => l_po_header_id
,x_return_status => l_return_status);
insert_po_distributions(p_po_dist_tab => l_po_dist_tab
,x_return_status => l_return_status);
my_debug('1780: Unexpected error from insert_po_distributions',4);
my_debug('1800: Error from insert_po_distributions',4);
,argument4 => 'N' -- Create or update items
,argument5 => 'N' -- Create sourcing rules
,argument6 => null -- Approval status
,argument7 => null -- Release generation method
,argument8 => l_batch_id -- Batch Id
,argument9 => null -- Operating unit
);
my_debug('1960: Attempting delete of hanging header related objects', 4);
DELETE okc_k_rel_objs rel
WHERE rel.chr_id = p_chr_id -- for the current contract
AND rel.cle_id IS NULL -- related obj pertains to header
AND rel.rty_code = g_crj_rty_code -- for PO creation
AND rel.jtot_object1_code = g_crj_chr_jtot_object_code -- correct jtot object
-- for contract header
AND NOT EXISTS (
SELECT null
FROM po_headers_all poh
WHERE rel.object1_id1 = poh.po_header_id
);
my_debug('1980: Related objects hanging headers deleted: ' || l_sql_rowcount, 4);
DELETE FROM okc_k_rel_objs
WHERE chr_id = p_chr_id;
my_debug('2040: Deleted related object lines, count = ' || SQL%ROWCOUNT, 4);
SELECT po.segment1
INTO l_po_number
FROM po_headers_all po
WHERE po.po_header_id =
( SELECT object1_id1
FROM okc_k_rel_objs rel
WHERE rel.chr_id = p_chr_id -- for the current contract
AND rel.cle_id IS NULL -- related obj pertains to header
AND rel.rty_code = g_crj_rty_code -- for PO creation
AND rel.jtot_object1_code = g_crj_chr_jtot_object_code
-- correct jtot object
);
my_debug('2080: Attempting delete of hanging line related objects', 1);
DELETE okc_k_rel_objs rel
WHERE rel.chr_id = p_chr_id -- for the current contract
AND rel.cle_id IS NOT NULL -- for line records
AND rel.rty_code = g_crj_rty_code -- for K-PO records
AND rel.jtot_object1_code = g_crj_cle_jtot_object_code -- correct jtot object
AND NOT EXISTS (
SELECT null
FROM po_lines_all pol
WHERE rel.object1_id1 = pol.po_header_id
AND rel.object1_id2 = pol.line_num);
my_debug('2100: Related objects hanging lines deleted: ' || l_sql_rowcount, 4);
UPDATE okc_k_rel_objs rel
SET (rel.object1_id1, rel.object1_id2) =
( SELECT pol.po_line_id, '#'
FROM po_lines_all pol
WHERE pol.po_header_id = rel.object1_id1
AND pol.line_num = rel.object1_id2 )
WHERE chr_id = p_chr_id
AND rel.rty_code = g_crj_rty_code
AND rel.cle_id IS NOT NULL;
my_debug('2120: Related objects lines updated: ' || l_sql_rowcount, 4);