OZF_FUNDS_PUB - erpSchools

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

SET serveroutput on;

DECLARE

l_user_name VARCHAR2(100);

l_user_id NUMBER;

l_resource_id NUMBER;

l_resp_id NUMBER;

l_api_version NUMBER := 1.0;

l_fund_id NUMBER;

l_validation_level NUMBER := fnd_api.g_valid_level_none;

l_return_status VARCHAR2(1);

l_msg_count NUMBER;

l_msg_data VARCHAR2(2000);

l_pvt_fund_rec OZF_FUNDS_PUB.fund_rec_type;

l_modifier_list_rec ozf_offer_pub.modifier_list_rec_type;

l_modifier_line_tbl ozf_offer_pub.modifier_line_tbl_type;

l_vo_pbh_tbl ozf_offer_pub.vo_disc_tbl_type;

l_vo_dis_tbl ozf_offer_pub.vo_disc_tbl_type;

l_vo_prod_tbl ozf_offer_pub.vo_prod_tbl_type;

l_qualifier_tbl ozf_offer_pub.qualifiers_tbl_type;

l_vo_mo_tbl ozf_offer_pub.vo_mo_tbl_type;

BEGIN

select resource_id

into l_pvt_fund_rec.owner

from JTF_RS_RESOURCE_EXTNS

where user_id = 1177;

BEGIN

select responsibility_id

into l_resp_id

from fnd_responsibility_vl

where responsibility_key = ‘OZF_USER’;

EXCEPTION

WHEN OTHERS THEN

l_resp_id := NULL;

END;

FND_GLOBAL.apps_initialize (

user_id => 1234,

resp_id => l_resp_id, — Oracle trade management user

resp_appl_id => 682 — Oracle Trade management

);

FND_PROFILE.put (‘FND_AS_MSG_LEVEL_THRESHOLD’, FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);

FND_MSG_PUB.initialize;

l_pvt_fund_rec.category_id := 1234; — ams_categories_tl (table)

l_pvt_fund_rec.short_name := ‘Test Budget’;

l_pvt_fund_rec.description := ‘Test Budget Descrition’;

l_pvt_fund_rec.currency_code_tc := ‘USD’;

l_pvt_fund_rec.start_date_active := SYSDATE;

l_pvt_fund_rec.end_date_active := null;

l_pvt_fund_rec.fund_type := ‘FIXED’;–‘FULLY_ACCRUED’;

l_pvt_fund_rec.user_status_id := 2100; –for DRAFT status

l_pvt_fund_rec.status_code := ‘draft’;

l_pvt_fund_rec.custom_setup_id := 2000;

l_pvt_fund_rec.original_budget := 999;

l_pvt_fund_rec.org_id := 12;

l_pvt_fund_rec.holdback_amt := 0;

l_pvt_fund_rec.ledger_id := 123;

l_pvt_fund_rec.attribute1 := ‘ABC’;

l_pvt_fund_rec.attribute2 := ‘XYZ’;

— discount rules

OZF_FUNDS_PUB.Create_fund(

p_api_version => l_api_version

,p_init_msg_list => FND_API.g_true

,p_commit => FND_API.g_false

,p_validation_level => l_validation_level

,x_return_status => l_return_status

,x_msg_count => l_msg_count

,x_msg_data => l_msg_data

,p_fund_rec => l_pvt_fund_rec

,p_modifier_list_rec => l_modifier_list_rec

,p_modifier_line_tbl => l_modifier_line_tbl

,p_vo_pbh_tbl => l_vo_pbh_tbl

,p_vo_dis_tbl => l_vo_dis_tbl

,p_vo_prod_tbl => l_vo_prod_tbl

,p_qualifier_tbl => l_qualifier_tbl

,p_vo_mo_tbl => l_vo_mo_tbl

,x_fund_id => l_fund_id

);

dbms_output.put_line(‘create returns ‘ || l_return_status);

dbms_output.put_line(‘l_fund_id ‘ || l_fund_id);

dbms_output.put_line(‘l_msg_count ‘ || l_msg_count);

IF l_return_status <> ‘S’ THEN

for i in 1..l_msg_count loop

dbms_output.put_line(substr(fnd_msg_pub.get(p_msg_index => i, p_encoded => ‘F’), 1, 254));

end loop;

ROLLBACK;

ELSE

COMMIT;

END IF;

exception

when others then

rollback;

end;

1

2

3

4

Select * from ozf_funds_all_b where fund_id = 1234;

Select * from ozf_funds_all_tl where fund_id = 1234;

–Navigation to view the Budget

–Oracle Trademanagement User -> Funds -> Search for the Budget

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

SET serveroutput on;

DECLARE

l_act_product_rec_type OZF_FUNDS_PUB.act_product_rec_type;

x_return_status VARCHAR2(2000);

x_msg_count NUMBER;

x_msg_data VARCHAR2(2000);

l_act_product_id NUMBER;

BEGIN

FND_GLOBAL.apps_initialize (

user_id => 1177,

resp_id => 22371, — Oracle trade management user

resp_appl_id => 682 — Oracle Trade management

);

FND_PROFILE.put (‘FND_AS_MSG_LEVEL_THRESHOLD’, FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);

FND_MSG_PUB.initialize;

l_act_product_rec_type.act_product_used_by_id:=1234; — Fund_id

l_act_product_rec_type.arc_act_product_used_by :=’FUND’;

l_act_product_rec_type.CATEGORY_ID :=4567;

l_act_product_rec_type.CATEGORY_SET_ID:=1000789;

l_act_product_rec_type.LEVEL_TYPE_CODE:=’FAMILY’;

l_act_product_rec_type.primary_product_flag :=’Y’;

OZF_FUNDS_PUB.create_product_eligibility(

p_api_version => 1.0 –l_api_version

,p_act_product_rec =>l_act_product_rec_type

,x_return_status =>x_return_status

,x_msg_count =>x_msg_count

,x_msg_data =>x_msg_data

,x_act_product_id =>l_act_product_id

);

IF x_return_status <> ‘S’

THEN

FOR I IN 1..x_msg_count LOOP

dbms_output.put_line(substr(fnd_msg_pub.get(p_msg_index => i, p_encoded => ‘F’), 1, 254));

END LOOP;

ROLLBACK;

ELSE

COMMIT;

END IF;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END;

1 2	— Search for the Products created for the Fund select * from ams_act_products where arc_Act_product_used_by = ‘FUND’ and act_product_used_by_id = 10327;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

SET serveroutput on;

DECLARE

l_mks_rec_type OZF_FUNDS_PUB.mks_rec_type;

x_return_status VARCHAR2(2000);

x_msg_count NUMBER;

x_msg_data VARCHAR2(2000);

l_act_mks_id NUMBER;

BEGIN

FND_GLOBAL.apps_initialize (

user_id => 1177,

resp_id => 22371, — Oracle trade management user

resp_appl_id => 682 — Oracle Trade management

);

FND_PROFILE.put (‘FND_AS_MSG_LEVEL_THRESHOLD’, FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);

FND_MSG_PUB.initialize;

— TO CALL Market Eligibility API

l_mks_rec_type.act_market_segment_used_by_id := 1234

l_mks_rec_type.arc_act_market_segment_used_by := ‘FUND’;

l_mks_rec_type.segment_type := ‘TERRITORY’;

l_mks_rec_type.market_segment_id := 1234;

OZF_FUNDS_PUB.create_market_segment(

p_api_version => 1.0 –l_api_version

,p_init_msg_list => fnd_api.g_false

,p_commit => fnd_api.g_false

,p_validation_level => fnd_api.g_valid_level_full

,p_mks_rec => l_mks_rec_type

,x_return_status => x_return_status

,x_msg_count => x_msg_count

,x_msg_data => x_msg_data

,x_act_mks_id => l_act_mks_id );

IF x_return_status <> ‘S’

THEN

FOR I IN 1..x_msg_count LOOP

dbms_output.put_line(substr(fnd_msg_pub.get(p_msg_index => i, p_encoded => ‘F’), 1, 254));

END LOOP;

ROLLBACK;

ELSE

COMMIT;

END IF;

EXCEPTION

WHEN OTHERS THEN

ROLLBACK;

END;

1 2	— Search for the market Segmetns created for the fund select * from ams_Act_market_Segments where arc_Act_market_segment_used_by = ‘FUND’ and act_market_Segment_used_by_id = 1234;