Hi Team,
I am currently working on Oracle 11.2.0.3
Parent Child1 C2 C3 C4 C5 C6 C7 C8 C9 C10 C11 C12
1234 01(c1) 02(c7)
2342 02 05
2342 04(c13) 11(c24)
2342 11(C31)
3456
......
Our current logic is taking care of a single parent who is having 12 childs.( the below is the one for C1 and C7 as these two are having values)
Below is teh logic we have written in of our procedure which will call our below
function
: LOAD_FUN (EMP.EMI_CK,
XREF1.CSTM_FIELD1_VALUE,
P_YEAR),
SUM (NVL (INSTG.ACC_APPLIED_AMOUNT1, 0))
OVER (PARTITION BY INSTG.CARDHOLDER_ID, INSTG.MCO_NAME)
+ SUM (NVL (STG.ACC_APPLIED_AMOUNT7, 0))
OVER (PARTITION BY STG.CARDHOLDER_ID, STG.MCO_NAME)
we are having 12 children groups with some specific names for each parent. After 12 if new 13th child added he/she shoud go to the resepective Child group within those 12.
If new child added to same parent then that new child go add to the next line for the same parent
as position C13/cn..
I need to apply a logic ( need to imlement Parent Child hierarchy upto N childs) and
which will calculate the sum for dynamycally added Childs and Parent..
Below are the tables and Procedure/Function have created.
Req_Hierarchical_Script.txt (58.52 KB)
Please check and scripts and Table Structures and let me know how can we implement the logic for 1 parent to multiple dynamically created Childsgroup providing the unit numbers once its completed 12 . These 12 are having some diffent names. So after 12 the next one should go to respective which childgroup only. Like this need to map.
Thanks
Amy.