API to Update External Payee
API to Update External Payee
Whenever a supplier/supplier site is created oracle system will automatically create a payee for the supplier and supplier site. Once the Payee is created the user may want to update the payee details.
The API to update the payee is :
IBY_DISBURSEMENT_SETUP_PUB.update_external_vendor
iby_disbursement_setup_pub.update_external_payee
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_ext_payee_tab => p_external_payee_tab_type,
p_ext_payee_id_tab => p_ext_payee_id_tab_type,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_ext_payee_status_tab => l_payee_upd_status
);
p_external_payee_tab_type is a table type variable of external payees which will take in a number of fields of the table.
p_ext_payee_id_tab_type is a table type variable which will take in only variable : ext_payee_id
When updating an external payee the developer needs to consider the below:
the api will update all the columns which defined in the record type variable which is passed to the API , thus the developer when assigning values to the record type variable needs to assigne the new values to be updated as well as the old values to the API rec type.
The data updated will be reflected in the table iby_external_payees_all
#####################################################################
####################################################################
API TO CREATE CODE COMBINATIONS:
API TO CREATE CODE COMBINATIONS:
It may not always be the case that Account codes will be created by user from the front end. There may be scenarios sometimes where we need to have programs that will be creating the code combinations.
Oracle has provided the API's that can be used to create code combinations , we will be discussing a couple of them here.
Pre-requisits
Now we will discuss the API's:
‘SQLGL’,
‘GL#’,
chart_of_accts_id,
concatenated_segments,
‘V’,
SYSDATE,
‘ALL’, NULL, NULL, NULL, NULL,
FALSE,FALSE, NULL, NULL, NULL);
It may not always be the case that Account codes will be created by user from the front end. There may be scenarios sometimes where we need to have programs that will be creating the code combinations.
Oracle has provided the API's that can be used to create code combinations , we will be discussing a couple of them here.
Pre-requisits
- When trying to create the code combinations via the API is that in the Key Flexfields setup , for the KFF defined for your Accounting segments the check box Allow Dynamic Inserts need to be enabled, then only will the code combination be created else the API will never create the accounting segments.
- User needs to do initialization
- The Accounting flex-filed Value sets need to be populated with the values being used in the account being created
Now we will discuss the API's:
- fnd_flex_ext.get_ccid , this API will take in the input parameters and return the code combination id of the account segment created. In case the API is not able to create the account segmewnts for any reason or in case of an error the API will return 0.
API syntax:
fnd_flex_ext.get_ccid
(application_short_name => 'SQLGL',
key_flex_code => 'GL#',
structure_number => p_chart_of_accounts_id,
validation_date => SYSDATE
concatenated_segments => allsegments
);
The API takes in all these parameters as input:
application_short_name : this is the short name of the General Ledger Application
key_flex_code : this is to be hardcoded as GL#
structure_number : this is to be the chart of account id in which the account
is to be created
validation_date : Pass SYSDATE
concatenated_segments : the account code segments in concatenated format
Once the API is run , if the account creation is successful then the CCID of the account is returned else the program will return 0.
2. FND_FLEX_KEYVAL.VALIDATE_SEG , the API will take in the input parameters based
on which the account code will be created , the API returns a BOOLEAN message of
TRUE : signifying success or FALSE : in case API was not successful
API syntax:
FND_FLEX_KEYVAL.VALIDATE_SEGS(
‘CREATE_COMBINATION’,‘SQLGL’,
‘GL#’,
chart_of_accts_id,
concatenated_segments,
‘V’,
SYSDATE,
‘ALL’, NULL, NULL, NULL, NULL,
FALSE,FALSE, NULL, NULL, NULL);
If the API is successful the the account segment will be created and the API will
return a status TRUE , in case any error occurs he the account code creation will
fail and the API will return a status FALSE, In case of any error the API will
populate the Error in the fnd_message queue.
The user will have to retrieve the error message from there to see the error
occurred:
occurred:
l_chr_error_msg := fnd_flex_keyval.error_message
#############################################################
##############################################################
Query to Find Requisition Details :
Query to Find Requisition Details :
SELECT prl.line_num Line
, plt.line_type Line_Type
, prl.item_id prl_item_id
, msi.segment1 Item
, prl.item_revision Rev
, prl.need_by_date Need_By_Date
, prl.unit_meas_lookup_code Unit
, round(prl.quantity,2) Quantity_Amount
, prl.unit_price Unit_Price
, DECODE (PRL.order_type_lookup_code, /* <SERVICES FPJ> */
'FIXED PRICE', PRL.amount,
'RATE', PRL.amount,
NVL(PRL.quantity, 1) * PRL.unit_price) C_AMOUNT
, ppf.full_name Requestor
, plc.displayed_field Source_Type
, decode(prl.source_type_code,'INVENTORY',ood.organization_name||' - '||prl.source_subinventory,'VENDOR',prh.segment1||' - '||prl.suggested_vendor_name||' - '||prl.suggested_vendor_location||' - '||prl.suggested_vendor_contact||' - '||prl.suggested_buyer_id,null) Source
, prl.item_description Item_Description
, prd.req_line_quantity Distributions
, prl.justification Justification
, prl.requisition_header_id
, prl.requisition_line_id
FROM po_requisition_headers prh
, po_requisition_lines prl
, po_req_distributions prd
, po_line_types plt
, per_people_f ppf
, org_organization_definitions ood
, po_lookup_codes plc
, mtl_system_items msi
, mtl_categories mca
, gl_code_combinations gcc, financials_system_parameters fsp
, po_system_parameters psp
WHERE prh.segment1 = '1713'
AND prl.requisition_line_id = prd.requisition_line_id
AND prl.requisition_header_id = prh.requisition_header_id
AND prl.line_type_id = plt.line_type_id
AND prl.to_person_id = ppf.person_id (+)
AND prl.source_organization_id = ood.organization_id(+)AND plc.lookup_type = 'REQUISITION SOURCE TYPE'
AND plc.lookup_code = prl.source_type_code
AND nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id),0)
from financials_system_parameters fsp)
AND trunc(sysdate)
BETWEEN nvl(ppf.effective_start_date, trunc(sysdate))
AND nvl(ppf.effective_end_date, trunc(sysdate))
AND prl.item_id = msi.inventory_item_id(+)
AND msi.organization_id = 204
AND prl.category_id = mca.category_id
AND prd.code_combination_id = gcc.code_combination_id
AND nvl(prl.modified_by_agent_flag,'N') = 'N'
AND nvl(prl.cancel_flag,'N') != 'Y'
AND nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED'
ORDER BY prl.line_num
SELECT prl.line_num Line
, plt.line_type Line_Type
, prl.item_id prl_item_id
, msi.segment1 Item
, prl.item_revision Rev
, prl.need_by_date Need_By_Date
, prl.unit_meas_lookup_code Unit
, round(prl.quantity,2) Quantity_Amount
, prl.unit_price Unit_Price
, DECODE (PRL.order_type_lookup_code, /* <SERVICES FPJ> */
'FIXED PRICE', PRL.amount,
'RATE', PRL.amount,
NVL(PRL.quantity, 1) * PRL.unit_price) C_AMOUNT
, ppf.full_name Requestor
, plc.displayed_field Source_Type
, decode(prl.source_type_code,'INVENTORY',ood.organization_name||' - '||prl.source_subinventory,'VENDOR',prh.segment1||' - '||prl.suggested_vendor_name||' - '||prl.suggested_vendor_location||' - '||prl.suggested_vendor_contact||' - '||prl.suggested_buyer_id,null) Source
, prl.item_description Item_Description
, prd.req_line_quantity Distributions
, prl.justification Justification
, prl.requisition_header_id
, prl.requisition_line_id
FROM po_requisition_headers prh
, po_requisition_lines prl
, po_req_distributions prd
, po_line_types plt
, per_people_f ppf
, org_organization_definitions ood
, po_lookup_codes plc
, mtl_system_items msi
, mtl_categories mca
, gl_code_combinations gcc, financials_system_parameters fsp
, po_system_parameters psp
WHERE prh.segment1 = '1713'
AND prl.requisition_line_id = prd.requisition_line_id
AND prl.requisition_header_id = prh.requisition_header_id
AND prl.line_type_id = plt.line_type_id
AND prl.to_person_id = ppf.person_id (+)
AND prl.source_organization_id = ood.organization_id(+)AND plc.lookup_type = 'REQUISITION SOURCE TYPE'
AND plc.lookup_code = prl.source_type_code
AND nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id),0)
from financials_system_parameters fsp)
AND trunc(sysdate)
BETWEEN nvl(ppf.effective_start_date, trunc(sysdate))
AND nvl(ppf.effective_end_date, trunc(sysdate))
AND prl.item_id = msi.inventory_item_id(+)
AND msi.organization_id = 204
AND prl.category_id = mca.category_id
AND prd.code_combination_id = gcc.code_combination_id
AND nvl(prl.modified_by_agent_flag,'N') = 'N'
AND nvl(prl.cancel_flag,'N') != 'Y'
AND nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED'
ORDER BY prl.line_num
##################################################################
###################################################################
Query to Find Requisition Header Info
QUERY TO FIND REQUISITION HEADER INFO
SELECT prh.segment1 Requisition
, psp.manual_req_num_type req_num_type
, ppf.full_name Preparer
, prh.creation_date Creation_Date
, prh.type_lookup_code
, ppf1.full_name Approver
, t.type_name Req_type
, prh.description Description
, pah.note Note_To_Approver
, prh.requisition_header_id Req_header
FROM po_requisition_headers prh
, per_people_f ppf1
, per_people_f ppf
, po_action_history pah
, po_system_parameters psp
, PO_DOCUMENT_TYPES_ALL_TL T
, PO_DOCUMENT_TYPES_ALL_B B
WHERE prh.REQUISITION_HEADER_ID=11675
and NVL(PRH.contractor_requisition_flag, 'N') <> 'Y'
AND prh.preparer_id = ppf.person_id
AND nvl(ppf.business_group_id, 0) = (select nvl(max(fsp.business_group_id), 0)
from financials_system_parameters fsp)
AND nvl(pah.action_code,'SUBMIT') in ('SUBMIT', 'FORWARD', 'REJECT', 'APPROVE',
'APPROVE AND RESERVE', 'RESERVE', 'ACCEPT','RETURN')
--AND prh.segment1 = P_req_num_from
AND EXISTS (SELECT null
FROM po_requisition_lines prl
WHERE prl.requisition_header_id = prh.requisition_header_id
AND nvl(prl.modified_by_agent_flag,'N') = 'N'
AND nvl(prl.closed_code,'OPEN') != 'FINALLY CLOSED')
AND pah.object_id = prh.requisition_header_id
AND pah.employee_id = ppf1.person_id
AND pah.object_type_code = 'REQUISITION'
AND pah.object_sub_type_code = prh.type_lookup_code
AND pah.sequence_num =
(SELECT max(sequence_num)
FROM po_action_history pah
WHERE pah.object_id = prh.requisition_header_id
AND pah.object_type_code = 'REQUISITION'
AND pah.object_sub_type_code = prh.type_lookup_code)
and B.DOCUMENT_TYPE_CODE = T.DOCUMENT_TYPE_CODE
AND B.DOCUMENT_SUBTYPE = T.DOCUMENT_SUBTYPE
AND b.document_type_code = 'REQUISITION'
AND b.document_subtype = prh.type_lookup_code
AND NVL(B.ORG_ID, -99) = NVL(T.ORG_ID, -99)
AND NVL(B.ORG_ID,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1 ,1),' ',
NULL,SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99))
= NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ', NULL,
SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)
AND T.LANGUAGE = USERENV('LANG')
No comments:
Post a Comment