[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ORACLE_USERNAME
INTO l_schema_name
FROM FND_ORACLE_USERID WHERE
READ_ONLY_FLAG = 'U';
SELECT 1
INTO l_tmp
FROM ALL_PLSQL_OBJECT_SETTINGS
WHERE NAME = 'HZ_BES_BO_SITE_UTIL_PKG'
AND TYPE = 'PACKAGE BODY' AND OWNER = l_schema_name ;
. Select all the user mandated node for a given BO
. Write the procedure body until cursor definition
. Write the root node SQL
. For all the child nodes
. Figure out the number or right paranthesis for the parent and write them
. Write AND EXISTS
. Identify the exact SQL for the current node and write it.
. Write the necessary right paranthesis remaining
. Complete the sql stmt with ;
SELECT
-- ROWNUM,
lvl, bo_code, root_node_flag rnf, ENTITY_NAME,
DECODE(lvl,1,
DECODE(entity_name,'HZ_PARTIES',NULL,SUBSTRB(node_path,2,LENGTH(node_path))),
SUBSTRB(node_path, INSTR(node_path,'/', -1, 2)+1,
(INSTR(node_path,'/', -1, 1)-INSTR(node_path,'/', -1, 2)-1))) parent_node
FROM
(SELECT
sys_connect_by_path(BUSINESS_OBJECT_CODE, '/') node_path,
LEVEL lvl, CONNECT_BY_ISLEAF isleaf,
BUSINESS_OBJECT_CODE bo_code, CHILD_BO_CODE,ENTITY_NAME, root_node_flag
FROM hz_bus_obj_definitions
START WITH BUSINESS_OBJECT_CODE = c_p_bo_code AND
user_mandated_flag = 'Y'
CONNECT BY PRIOR CHILD_BO_CODE = BUSINESS_OBJECT_CODE AND
user_mandated_flag = 'Y'
ORDER BY LEVEL ASC)
WHERE isleaf = 1
ORDER BY node_path ASC, rnf desc;
First Record selected will always be root node of one of the four
business objects (Org, Person, Org Customer and Person Customer).
As there are no predecessors to first record, no need to write
the right paranthesis.
For the second record in collection, there is no need to do elaborate checking.
This is because, ALWAYS, previous record is the root node of the current record.
So for both first and second records, the only operatio that must be done is
to figure out the correct SQL.
From 3rd row onwards, figure out whether to write the right paranthesis
for the previous record or not. Example - at 3rd node, you will figure out
whether to write the right paranthesis for 2nd node or not.
If it must be written, how many rigt paranthesis must be written.
Then write them.
Figure out the correct SQL and write them to buffer.
If right paranthesis must not be written,
store it in hash map.
write the correct SQL to the buffer.
Note - the collection, which is called hash map here contains the
following pieces of information.
1. node for which the right paranthesis is stored
2. BO of the node
3. Parent BO of the node
4. right paranthesis
*/
FOR i IN 2..l_node_count LOOP
IF i >2 THEN
-- for the second node in the hierarchy, there is no need to
-- figure out right paranthesis. This is because, There are atleast two
-- mandatory nodes in any object. Org (hz_parties, org profiles).
-- Person (hz_parties, person profiles). Org Cust (hz_parties, one acct, one org)
-- Person Cust (hz_parties, one acct, one person)
-- So, the first node in an hierarchy never needs a right paranthesis at it's end.
-- Hence, when processing second node SQL, we do not need to process the
-- first node right paranthisis. Hence, we skip part to figure out the
-- right paranthesis for the second node and go directly to figuring out the
-- SQL for second node.
IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.debug(
p_message=>'the node that is being processed is: '||i,
p_prefix=>l_debug_prefix,
p_msg_level=>fnd_log.level_procedure);
-- once the row from current collection (l_rp_tbl) is deleted,
-- then we loose the parent BO of the row. we need this to
-- tie this (going to be deleted rec) to the previous rec in
-- collection to compare and see if it must be deleted or not.
-- So, l_gpvar acts as a temp store for storing the parent BO
l_gpvar := l_rp_tbl(l_rp_ct).PARENT_BO_CODE;
l_rp_tbl.DELETE(l_rp_ct);
-- check if (l_rp_tbl) collection rec getting deleted is not the
-- the parent of current node (in l_node_tbl) that is being processed.
-- If it is, do not delete the record from (l_rp_tbl) collection
-- and exit from this loop. This is because, current node, being
-- a child of the node in l_rp_tbl collection, it must not write
-- the rparanthesis.
/*
*/
EXIT WHEN (((l_node_tbl(i).PARENT_BO_CODE = l_node_tbl(l_chk_node).PARENT_BO_CODE) AND
(l_node_tbl(i).BO_CODE = l_node_tbl(l_chk_node).BO_CODE) AND
(l_node_tbl(l_chk_node).RNF = 'Y')) OR
((l_node_tbl(l_chk_node).BO_CODE = l_node_tbl(i).PARENT_BO_CODE) AND
(l_node_tbl(l_chk_node).RNF = 'Y'))
);
l_rp_tbl.DELETE(l_rp_ct);
l2i(l_rp||'-- for not in clause in delete statement');
l2i(l_rp||'-- for in clause in update statement');
-- Write ; at the end of select statemenet.
l_comment VARCHAR2(100) := 'To determine completeness of BOs. Non-complete BOs will be deleted';
For each BO, a separate delete statement must be generated.
Flow:
. generate procedure header by calling procBegin()
. generate debug mesg
. get BO Codes
. generate if statement to check if the parameter passed = G_PER_BO_CODE
. generate delete statements (by calling genBOSQL(G_PER_BO_CODE));
. if previous delete statement generation is successfull
. generate commit statement
. generate debug mesg
. if previous delete statement generation is un-successfull
. debug mesg
. generate debug mesg
. raise exception
. generate if statement to check if the parameter passed = G_ORG_BO_CODE
. generate delete statements (by calling genBOSQL(G_ORG_BO_CODE));
. if previous delete statement generation is successfull
. generate commit statement
. generate debug mesg
. if previous delete statement generation is un-successfull
. debug mesg
. generate debug mesg
. raise exception
. generate if statement to check if the parameter passed = G_PER_CUST_BO_CODE
. generate delete statements (by calling genBOSQL(G_PER_CUST_BO_CODE));
. if previous delete statement generation is successfull
. generate commit statement
. generate debug mesg
. if previous delete statement generation is un-successfull
. debug mesg
. generate debug mesg
. raise exception
. generate if statement to check if the parameter passed = G_ORG_CUST_BO_CODE
. generate delete statements (by calling genBOSQL(G_ORG_CUST_BO_CODE));
. if previous delete statement generation is successfull
. generate commit statement
. generate debug mesg
. if previous delete statement generation is un-successfull
. debug mesg
. generate debug mesg
. raise exception
. generate procedure tail by calling procEnd()
*/
-- To write the procedure header
procBegin (l_procName, l_comment, l_param_name);
li('-- delete root nodes for BOs that are not complete');
l2i('-- delete statement for '||G_PER_BO_CODE||' BO');
-- generate delete statement for Person BO
li('IF P_BO_CODE ='''||G_PER_BO_CODE||''' THEN');
-- generate delete statements by calling
genBOSQL(G_PER_BO_CODE, l_sql_for, l_ret_status);
-- generate a commit statement after each delete stmt
l2i(' COMMIT; -- commiting the deletes done so far. ');
l2i('-- delete statement for '||G_ORG_BO_CODE||' BO');
-- generate delete statement for Org BO
li('IF P_BO_CODE ='''||G_ORG_BO_CODE||''' THEN');
-- generate a commit statement after each delete stmt
l2i(' COMMIT; -- commiting the deletes done so far.');
l2i('-- delete statement for '||G_PER_CUST_BO_CODE||' BO');
-- generate delete statement for Person Cust BO
li('IF P_BO_CODE ='''||G_PER_CUST_BO_CODE||''' THEN');
-- generate a commit statement after each delete stmt
l2i(' COMMIT; -- commiting the deletes done so far.');
l2i('-- delete statement for '||G_ORG_CUST_BO_CODE||' BO');
-- generate delete statement for Org Cust BO
li('IF P_BO_CODE ='''||G_ORG_CUST_BO_CODE||''' THEN');
-- generate a commit statement after each delete stmt
l2i(' COMMIT; -- commiting the deletes done so far.');
For each BO, a separate Update statement must be generated. Each of this update
statement will update all those rows that are candidates for Update event.
This update statement will go after all those records for which the event type
must be identified.
Flow:
. generate procedure header by calling procBegin()
. generate debug mesg
. get BO Codes
. generate if condition on P_BO_CODE = PERSON
. For PERSON BO generate the update statement
. add debug mesg
. generate COMMIT statement
. add debug mesg
. generate else if condition on P_BO_CODE = ORG
. For ORG BO generate the update statement
. add debug mesg
. generate COMMIT statement
. add debug mesg
. generate else if condition on P_BO_CODE = PERSON_CUST
. For PERSON_CUST BO generate the update statement
. add debug mesg
. generate COMMIT statement
. add debug mesg
. generate else if condition on P_BO_CODE = ORG_CUST
. For ORG_CUST BO generate the update statement
. add debug mesg
. generate COMMIT statement
. add debug mesg
. generate else condition
. add debug mesg
. generate raise exception statement.
. generate end if statement
. generate the procedure tail
*/
-- To write the procedure header
procBegin (l_procName, l_comment, l_param_name);
-- generate delete statements by calling
li('-- update nodes for BOs that are already complete');
li('-- update statement for '||G_PER_BO_CODE||' BO');
-- generate update statement for Person BO
li('IF P_BO_CODE ='''||G_PER_BO_CODE||''' THEN');
-- generate a commit statement after each delete stmt
li(' COMMIT; -- commiting the updates done so far for '||G_PER_BO_CODE);
l_msg := 'evt type update stmt executed for '||G_PER_BO_CODE||' BO';
fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_UPDATE_ERROR');
-- generate update statement for Org BO
li(' ');
-- generate a commit statement after each delete stmt
li(' COMMIT; -- commiting the updates done so far for '||G_ORG_BO_CODE);
l_msg := 'evt type update stmt executed for '||G_ORG_BO_CODE||' BO';
fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_UPDATE_ERROR');
-- generate update statement for Person Customer BO
li('IF P_BO_CODE ='''||G_PER_CUST_BO_CODE||''' THEN');
-- generate a commit statement after each delete stmt
li(' COMMIT; -- commiting the updates done so far for '||G_PER_CUST_BO_CODE);
l_msg := 'evt type update stmt executed for '||G_PER_CUST_BO_CODE||' BO';
fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_UPDATE_ERROR');
-- generate update statement for Org Customer BO
li('IF P_BO_CODE ='''||G_ORG_CUST_BO_CODE||''' THEN');
-- generate a commit statement after each delete stmt
li(' COMMIT; -- commiting the updates done so far for '||G_ORG_CUST_BO_CODE);
l_msg := 'evt type update stmt executed for '||G_ORG_CUST_BO_CODE||' BO';
fnd_message.set_name('AR', 'HZ_BES_BO_EVTYPE_UPDATE_ERROR');
Called from: Both BOD update and create APIs will calls this.
Called by: bo_gen_main()
*/
PROCEDURE gen_pkg_body
(P_STATUS OUT NOCOPY BOOLEAN)
IS
l_debug_prefix VARCHAR2(15) := 'GENPKG:';