[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT master_organization_id
INTO l_master_organization_id
FROM mtl_parameters
WHERE organization_id = p_line_rec.ship_from_org_id;
OE_DEBUG_PUB.Add('Before calling update flow status to...'|| 'AWAITING EXPORT SCREENING' ) ;
OE_ORDER_WF_UTIL.Update_Flow_Status_Code
(p_line_id => p_line_rec.line_id
,p_flow_status_code => 'AWAITING_EXPORT_SCREENING'
,x_return_status => l_return_status
);
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => p_line_rec.line_id
,p_flow_status_code => 'EXPORT_SCREENING_DATA_ERROR'
,x_return_status => l_return_status
);
SELECT PARTY.PARTY_NAME, party.party_id, party.party_number
INTO x_contact_name, x_contact_hz_id, x_contact_number --Bug 9583024
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE,
HZ_PARTIES PARTY,
HZ_CUST_ACCOUNTS ACCT,
HZ_RELATIONSHIPS REL,
HZ_PARTIES REL_PARTY
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = p_contact_id
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND REL.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND REL.SUBJECT_ID = PARTY.PARTY_ID
AND REL.PARTY_ID = REL_PARTY.PARTY_ID
AND REL.OBJECT_ID = ACCT.PARTY_ID
AND ACCT.CUST_ACCOUNT_ID = ACCT_ROLE.CUST_ACCOUNT_ID;
SELECT
site_uses.site_use_id source_id,
decode(cp_source_type, 'END_CUSTOMER', cp_source_type, site_uses.site_use_code) source_type, --Bug 9583024
party.party_name party_name,
loc.address1 address1,
loc.address2 address2,
loc.address3 address3,
loc.address4 address4,
-- ltrim(rtrim(loc.address1||','||loc.address2||
-- ','||loc.address3||','||loc.address4)) address,
loc.city city,
loc.state state,
loc.country country,
loc.postal_code postal_code,
decode(party.party_type,'PERSON',hp.person_name_phonetic, party.organization_name_phonetic ) alternate_name,
party.party_id hz_party_id, -- Bug 9583024
party.party_number hz_party_number , -- Bug 9583024
party_site.party_site_number party_site_number -- Bug 9583024
FROM
hz_cust_site_uses_all site_uses,
hz_cust_acct_sites_all acct_site,
hz_party_sites party_site,
hz_locations loc,
hz_cust_accounts cust_acct,
hz_parties party,
hz_Person_profiles hp
WHERE
site_uses.cust_acct_site_id = acct_site.cust_acct_site_id
AND acct_site.cust_account_id = cust_acct.cust_account_id
AND party.party_id = cust_acct.party_id
AND acct_site.party_site_id = party_site.party_site_id
AND loc.location_id = party_site.location_id
AND site_uses.site_use_code = decode(cp_source_type, 'END_CUSTOMER', site_uses.site_use_code, cp_source_type) --Bug 9583024
AND site_uses.site_use_id = cp_source_id
AND hp.party_id(+) = party.party_id -- bug 4231894
AND rownum = 1;
SELECT
hu.organization_id source_id,
'SHIP_FROM' source_type,
hu.name party_name,
hl.address_line_1 address1,
hl.address_line_2 address2,
hl.address_line_3 address3,
-- ltrim(rtrim(hl.address_line_1||','||
-- hl.address_line_2||','||hl.address_line_3)) address,
hl.town_or_city city,
hl.region_2 state,
hl.country country,
hl.postal_code postal_code,
hl.telephone_number_1 phone,
NULL email,
NULL fax,
NULL url
FROM
hr_all_organization_units hu,
hr_locations hl
WHERE
hl.location_id = hu.location_id
AND hu.organization_id = cp_ship_from_org_id;
SELECT
cust_acct.cust_account_id source_id,
'SOLD_TO' source_type,
party.party_name party_name,
loc.address1 address1,
loc.address2 address2,
loc.address3 address3,
loc.address4 address4,
-- ltrim(rtrim(loc.address1||','||loc.address2
-- ||','||loc.address3||','||loc.address4)) address,
loc.city city,
loc.state state,
loc.country country,
loc.postal_code postal_code,
decode(party.party_type,'ORGANIZATION',party.organization_name_phonetic
,hp.person_name_phonetic) alternate_name, -- bug4231894
party.party_id hz_party_id , -- Bug 9583024
party.party_number hz_party_number , -- Bug 9583024
party_site.party_site_number party_site_number -- Bug 9583024
FROM
hz_parties party,
hz_cust_accounts cust_acct,
hz_locations loc,
hz_party_sites party_site,
hz_Person_profiles hp
WHERE
party.party_id = cust_acct.party_id
AND cust_acct.cust_account_id = cp_sold_to_org_id
AND party_site.party_id = party.party_id
AND loc.location_id = party_site.location_id
AND hp.party_id(+) = party.party_id -- bug 4231894
AND rownum = 1;
| Name : Update_Process_Flag |
| Parameters : IN p_line_id |
| IN p_header_id |
| |
| Description : This Procedure checks whether any |
| requests exists for the line id |
| with process flag not equal to 4 |
| and calls Update_Process_Flag. |
+-----------------------------------------------------*/
PROCEDURE Update_Process_Flag(
p_header_id IN NUMBER, -- Bug 11797031
p_line_id IN NUMBER
) IS
l_request_control_id_list WSH_ITM_UTIL.CONTROL_ID_LIST;
oe_debug_pub.add('Entering update process flag..' , 4 ) ;
SELECT request_control_id
BULK COLLECT
INTO l_request_control_id_list
FROM WSH_ITM_REQUEST_CONTROL
WHERE application_id = 660
AND original_system_line_reference = p_line_id
AND original_system_reference = p_header_id -- Bug 11797031
AND Process_flag <> 4;
WSH_ITM_UTIL.Update_process_Flag(
l_request_control_id_list,
4,
x_return_status);
oe_debug_pub.add('Update process flag returned with ..'|| x_return_status ,1);
oe_debug_pub.add('Exiting update process flag..',4 ) ;
END Update_Process_Flag;
| Description : This Procedure inserts records into |
| Request interface tables |
| |
+-----------------------------------------------------*/
PROCEDURE Create_Request
( p_master_organization_id IN NUMBER
, p_line_rec IN OE_ORDER_PUB.line_rec_type
, x_return_status OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Create_Request';
SELECT decode (wits.value, 'FALSE', 'N', null, 'N', 'TRUE', 'Y', 'N')
FROM wsh_itm_parameter_setups_b wits
WHERE wits.PARAMETER_NAME = 'WSH_ITM_INTG_GTM';
Update_Process_Flag(p_line_rec.header_id, p_line_rec.line_id); -- Bug 11797031
SELECT sold_to_site_use_id
INTO l_sold_to_site_use_id
FROM oe_order_headers_all
WHERE header_id = p_line_rec.header_id;
SELECT sold_to_contact_id
INTO l_sold_to_contact_id
FROM oe_order_headers_all
WHERE header_id = p_line_rec.header_id;
SELECT wsh_itm_request_set_s.NEXTVAL
INTO l_request_set_id
FROM dual;
SELECT wsh_itm_request_control_s.NEXTVAL
INTO l_request_control_id
FROM dual;
SELECT order_number,order_type,cust_po_number,
transactional_curr_code,conversion_type_code,
conversion_rate,ordered_date, terms
INTO l_order_number,l_order_type,l_cust_po_number,
l_transactional_curr_code,l_conversion_type_code,
l_conversion_rate,l_ordered_date,l_payment_term_name
FROM oe_order_headers_v
WHERE header_id = p_line_rec.header_id; */
SELECT h.order_number, ot.name, h.cust_po_number, h.transactional_curr_code,
h.conversion_type_code, h.conversion_rate, h.ordered_date, term.name
INTO l_order_number, l_order_type, l_cust_po_number, l_transactional_curr_code,
l_conversion_type_code, l_conversion_rate, l_ordered_date, l_payment_term_name
FROM oe_order_headers h, oe_transaction_types_tl ot, ra_terms_tl term
WHERE h.header_id = p_line_rec.header_id
AND h.order_type_id = ot.transaction_type_id
AND ot.language = userenv('LANG')
AND h.payment_term_id = term.term_id(+)
AND term.Language(+) = userenv('LANG');
SELECT organization_code
INTO l_organization_code
FROM mtl_parameters
WHERE organization_id = p_line_rec.ship_from_org_id;
INSERT INTO WSH_ITM_REQUEST_CONTROL (
REQUEST_CONTROL_ID,
REQUEST_SET_ID,
APPLICATION_ID,
MASTER_ORGANIZATION_ID,
ORGANIZATION_CODE,
APPLICATION_USER_ID,
SERVICE_TYPE_CODE,
TRANSACTION_DATE,
SHIP_FROM_COUNTRY_CODE,
SHIP_TO_COUNTRY_CODE,
ORIGINAL_SYSTEM_REFERENCE,
ORIGINAL_SYSTEM_LINE_REFERENCE,
PROCESS_FLAG,
RESPONSE_HEADER_ID,
DEBUG_FLAG,
ONLINE_FLAG,
ATTRIBUTE1_NAME,
ATTRIBUTE2_NAME,
ATTRIBUTE3_NAME,
ATTRIBUTE4_NAME,
ATTRIBUTE5_NAME,
ATTRIBUTE6_NAME,
ATTRIBUTE7_NAME,
ATTRIBUTE8_NAME,
ATTRIBUTE9_NAME,
ATTRIBUTE10_NAME,
ATTRIBUTE11_NAME,
ATTRIBUTE12_NAME,
ATTRIBUTE13_NAME,
ATTRIBUTE14_NAME,
ATTRIBUTE15_NAME,
ATTRIBUTE1_VALUE,
ATTRIBUTE2_VALUE,
ATTRIBUTE3_VALUE,
ATTRIBUTE4_VALUE,
ATTRIBUTE5_VALUE,
ATTRIBUTE6_VALUE,
ATTRIBUTE7_VALUE,
ATTRIBUTE8_VALUE,
ATTRIBUTE9_VALUE,
ATTRIBUTE10_VALUE,
ATTRIBUTE11_VALUE,
ATTRIBUTE12_VALUE,
ATTRIBUTE13_VALUE,
ATTRIBUTE14_VALUE,
ATTRIBUTE15_VALUE,
ORDER_NUMBER,
ORDER_TYPE,
OPERATING_UNIT,
CUST_PO_NUM ,
TRANSACTIONAL_CURR_CODE ,
CONVERSION_TYPE_CODE,
CONVERSION_RATE,
ORDERED_DATE,
SHIPPING_METHOD_CODE ,
REQUEST_DATE,
FREIGHT_TERMS_CODE,
PAYMENT_NAME,
PAYMENT_TERM_ID,
ORDERED_QUANTITY,
ORDERED_QUANTITY_UOM, --bug 3640122
LINE_NUMBER,
ORDER_LINE_NUMBER, --Bug 5647666
UNIT_LIST_PRICE,
UNIT_SELLING_PRICE,
TRIGGERING_POINT,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
ORGANIZATION_ID, --Added for bug 6639636
TOP_MODEL_LINE_ID, -- Added for ER 6490366
ATO_LINE_ID, -- Bug 9670588
LINK_TO_LINE_ID, -- Bug 9670588
SPLIT_FROM_LINE_ID -- Bug 9670588
)
VALUES (
l_request_control_id,
l_request_set_id,
660,
p_master_organization_id,
l_organization_code,
FND_GLOBAL.USER_ID,
l_service_types,
sysdate,
Address_table(1).add_party_country,
Address_table(2).add_party_country,
p_line_rec.header_id,
p_line_rec.line_id,
0,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
l_order_number,
l_order_type,
p_line_rec.org_id,
l_cust_po_number,
l_transactional_curr_code,
l_conversion_type_code,
l_conversion_rate,
l_ordered_date,
p_line_rec.shipping_method_code,
p_line_rec.request_date,
p_line_rec.freight_terms_code,
l_payment_term_name,
p_line_rec.payment_term_id,
p_line_rec.ordered_quantity,
p_line_rec.order_quantity_uom,--bug 3640122
p_line_rec.line_number,
l_order_line_number, --Bug 5647666
p_line_rec.unit_list_price,
p_line_rec.unit_selling_price,
'ORDER_SCHEDULING',
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
p_line_rec.ship_from_org_id, --Added for bug 6639636
p_line_rec.top_model_line_id, -- Added for bug 6490366
p_line_rec.ato_line_id, --bug 9670588
p_line_rec.link_to_line_id , -- Bug 9670588
p_line_rec.split_from_line_id -- Bug 9670588
);
oe_debug_pub.add('Inserted record in to wsh_itm_request_control...' , 1 ) ;
SELECT concatenated_segments into l_product_code
FROM mtl_system_items_vl
WHERE inventory_item_id = p_line_rec.inventory_item_id
AND organization_id = p_line_rec.ship_from_org_id; --l_organization_code ;
Select MEANING INTO l_Item_type
FROM MTL_SYSTEM_ITEMS_B items, FND_LOOKUP_VALUES FLV
WHERE items.INVENTORY_ITEM_ID = p_line_rec.inventory_item_id
AND items.ORGANIZATION_ID = p_line_rec.ship_from_org_id --l_organization_code
AND FLV.LOOKUP_CODE = items.ITEM_TYPE AND FLV.LOOKUP_TYPE = 'ITEM_TYPE'
AND FLV.VIEW_APPLICATION_ID = 3
AND FLV.LANGUAGE = userenv('LANG')
AND FLV.ENABLED_FLAG = 'Y' ;
INSERT INTO WSH_ITM_ITEMS (
ITEM_ID,
REQUEST_CONTROL_ID,
INVENTORY_ITEM_ID,
ORGANIZATION_CODE,
OPERATING_UNIT,
PRODUCT_CODE,
ITEM_TYPE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
)
VALUES (
wsh_itm_items_s.NEXTVAL,
l_request_control_id,
p_line_rec.inventory_item_id,
l_organization_code,
p_line_rec.org_id,
l_product_code,
l_Item_type,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID
);
oe_debug_pub.add('Inserted record in to wsh_itm_items...' , 1 ) ;
SELECT wsh_itm_parties_s.NEXTVAL
INTO l_party_id
FROM dual;
INSERT INTO WSH_ITM_PARTIES (
PARTY_ID,
REQUEST_CONTROL_ID,
ORIGINAL_SYSTEM_REFERENCE,
ORIGINAL_SYSTEM_LINE_REFERENCE,
SOURCE_ORG_ID,
PARTY_TYPE,
PARTY_NAME,
ALTERNATE_NAME, -- BUG 4231894
PARTY_ADDRESS1,
PARTY_ADDRESS2,
PARTY_ADDRESS3,
PARTY_ADDRESS4,
PARTY_ADDRESS5,
PARTY_CITY,
PARTY_STATE,
PARTY_COUNTRY_CODE,
PARTY_COUNTRY_NAME,
POSTAL_CODE,
CONTACT_NAME,
PHONE,
EMAIL,
FAX,
WEB,
ATTRIBUTE1_NAME,
ATTRIBUTE2_NAME,
ATTRIBUTE3_NAME,
ATTRIBUTE4_NAME,
ATTRIBUTE5_NAME,
ATTRIBUTE6_NAME,
ATTRIBUTE7_NAME,
ATTRIBUTE8_NAME,
ATTRIBUTE9_NAME,
ATTRIBUTE10_NAME,
ATTRIBUTE11_NAME,
ATTRIBUTE12_NAME,
ATTRIBUTE13_NAME,
ATTRIBUTE14_NAME,
ATTRIBUTE15_NAME,
ATTRIBUTE1_VALUE,
ATTRIBUTE2_VALUE,
ATTRIBUTE3_VALUE,
ATTRIBUTE4_VALUE,
ATTRIBUTE5_VALUE,
ATTRIBUTE6_VALUE,
ATTRIBUTE7_VALUE,
ATTRIBUTE8_VALUE,
ATTRIBUTE9_VALUE,
ATTRIBUTE10_VALUE,
ATTRIBUTE11_VALUE,
ATTRIBUTE12_VALUE,
ATTRIBUTE13_VALUE,
ATTRIBUTE14_VALUE,
ATTRIBUTE15_VALUE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
-- Bug 9583024
HZ_PARTY_ID,
HZ_PARTY_NUMBER ,
PARTY_SITE_NUMBER ,
CONTACT_HZ_PARTY_ID ,
CONTACT_HZ_PARTY_NUMBER
)
VALUES (
l_party_id,
l_request_control_id,
p_line_rec.header_id,
p_line_rec.line_id,
Address_table(J).add_source_orgid,
Address_table(J).add_source_type,
Address_table(J).add_party_name,
Address_table(J).add_alternate_name, --bug 4231894
Address_table(J).add_party_address1,
Address_table(J).add_party_address2,
Address_table(J).add_party_address3,
Address_table(J).add_party_address4,
null,
Address_table(J).add_party_city,
Address_table(J).add_party_state,
Address_table(J).add_party_country,
Address_table(J).add_party_country,
Address_table(J).add_party_postal_code,
Address_table(J).add_party_contact_name,
Address_table(J).add_party_phone,
Address_table(J).add_party_email,
Address_table(J).add_party_fax,
Address_table(J).add_party_url,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
sysdate,
FND_GLOBAL.USER_ID,
FND_GLOBAL.USER_ID,
sysdate,
FND_GLOBAL.USER_ID,
-- Bug 9583024
Address_Table(J).add_hz_party_id ,
Address_Table(J).add_hz_party_number ,
Address_Table(J).add_party_site_number ,
Address_Table(J).add_contact_hz_party_id,
Address_Table(J).add_contact_hz_party_number
);
oe_debug_pub.add( 'INSERTED '||J||' RECORDS IN TO WSH_ITM_PARTIES...' ) ;
END LOOP; -- Loop for inserting records in to Itm Parties
END LOOP; -- Loop for inserting records in to Request Control
SELECT wl.Error_Text,wl.denied_party_flag,wp.Party_name
FROM wsh_itm_response_lines wl,
wsh_itm_parties wp
WHERE wl.Response_header_id = cp_response_header_id
AND wp.party_id = wl.party_id;
SELECT request_control_id,response_header_id,organization_id,
nvl(original_system_line_reference,0) line_id,
nvl(original_system_reference,0) header_id --bug 4503620
FROM wsh_itm_request_control wrc
WHERE request_control_id = nvl(cp_request_control_id,0)
AND wrc.application_id = 660
UNION
SELECT request_control_id,response_header_id,organization_id,
nvl(original_system_line_reference,0) line_id,
nvl(original_system_reference,0) header_id --bug 4503620
FROM wsh_itm_request_control wrc
WHERE request_set_id = nvl(cp_request_set_id,0)
AND wrc.application_id = 660;
oe_export_compliance_wf.update_screening_result(...)
Line will not be progressed from the export_screening_compliance if
it is in system error.
*/
Oe_debug_pub.ADD('Req control ID / Req set ID / Status code : ' ||
p_request_control_id || '/' || p_request_set_id ||
'/' || p_status_code);
SELECT ORG_ID
INTO l_org_id
FROM oe_order_lines_all
WHERE line_id = l_line_id;
SELECT wpa.activity_name -- Bug 10361575
INTO l_activity
FROM wf_item_activity_statuses wias, wf_process_activities wpa
WHERE wias.item_type = 'OEOL'
AND wias.item_key = To_Char(l_line_id)
AND wias.process_activity = wpa.instance_id
AND wias.activity_status = 'NOTIFIED';
SELECT error_text
INTO l_error_text
FROM wsh_itm_response_headers
WHERE response_header_id = l_response_header_id;
SELECT top_model_line_id
INTO l_top_model_line_id
FROM oe_order_lines
WHERE line_id = l_line_id;
SELECT '1'
INTO l_dummy
FROM oe_order_lines_all
WHERE line_id= l_top_model_line_id
FOR UPDATE; --Commented for bug 6415831 --nowait;
SELECT '1'
INTO l_dummy
FROM oe_order_lines_all
WHERE line_id= l_line_id
FOR UPDATE; --Commented for bug 6415831 --nowait;
OE_ORDER_WF_UTIL.Update_Flow_Status_Code (
p_line_id => l_line_id,
p_flow_status_code => 'EXPORT_SCREENING_COMPLETED',
x_return_status => l_return_status
);
SELECT wpa.instance_label INTO l_activity
FROM wf_item_activity_statuses wias, wf_process_activities wpa
WHERE wias.item_type = 'OEOL'
AND wias.item_key = To_Char(l_line_id)
AND wias.process_activity = wpa.instance_id
AND wias.activity_status = 'NOTIFIED';
SELECT error_text
INTO l_error_text
FROM wsh_itm_response_headers
WHERE response_header_id = l_response_header_id;
OE_ORDER_WF_UTIL.Update_Flow_Status_Code
(p_line_id => l_line_id,
p_flow_status_code => 'EXPORT_SCREENING_DATA_ERROR',
x_return_status => l_return_status
);
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => l_line_id,
p_flow_status_code => 'EXPORT_SCREENING_COMPLETED',
x_return_status => l_return_status
);
OE_Order_WF_Util.Update_Flow_Status_Code
(p_line_id => l_line_id,
p_flow_status_code => 'EXPORT_SCREENING_COMPLETED',
x_return_status => l_return_status
);
OE_ORDER_WF_UTIL.Update_Flow_Status_Code
(p_line_id => l_line_id,
p_flow_status_code => 'EXPORT_SCREENING_COMPLETED',
x_return_status => l_return_status
);